I have written another blog article previously about how to set up your Power BI to get data from a file in OneDrive for Business folder without needing gateway. That was a popular blog article, but I got questions about what if I want to get data from a folder in OneDrive for business but still want to do it without gateway for automatic refresh. Let’s see what is the solution.
Get Data from Folder
The Get data from folder feature in Power BI Desktop works as one of the powerful features of Power Query. if you select a folder, you can expand to all files under that folder.
As you can see, I get data from a folder in OneDrive for Business. which means I can get all files under that folder.
which means I can get all files under that folder. And it also gives me the ability to combine the content of files.
The Problem in the Service
This feature gives you the ability to combine files with the same content very simply using the graphical interface of Power Query Editor. For the purpose of this blog article, it is enough to know that it creates a function from one of the files, and then run through it for all files in that folder to combine them all.
The solution works perfectly until you publish it to the Power BI service, then you would get to the problem of needing a gateway. Similar to what I explained in my other article about one file. On the website, using the example above, you need to set up the gateway.
There is no problem with setting up the gateway, of course, however, you won’t need it for a data source in the cloud, and OneDrive for Business is also in the cloud.
Folder.Files
When I dig deeper into the Power Query function, I can see that the problem is in Folder.Files function. This function only works with local folders.
You cannot provide a web URL of OneDrive for Business here;
SharePoint.Files or SharePoint.Contents
The trick is to use SharePoint.Files or SharePoint.Contents function of Power Query with the root URL for OneDrive for Business.
To use this trick, find the query that combines all the files (usually named as the folder name that you fetched the files from it) then change the Folder.Files (the very first step) with something like below: (if you don’t see the formula bar, enable it in Power Query Editor, in the View tab, Formula Bar checkbox)
One way to get your OneDrive for business URL is to log in to that through a web browser (you can just google for OneDrive for Business Login). This is for example, my web URL for OneDrive for Business:
Then use that path (highlighted above, only before _layouts or any folder name) inside the SharePoint.Contents function:
= SharePoint.Contents("https://radacad-my.sharepoint.com/personal/reza_radacad_com/")
If you notice, I haven’t went down to the specific folder, SharePoint.Contents will give me Folders inside that URL, SharePoint.Files will give me Files in that URL. The Content usually gives you an easier way to navigate to the folder you want like below;
Start by creating a new Blank Query, name it as FitBit:
Then paste the code into the formula bar (if you don’t see the formula bar, enable it from the View tab);
Navigate down to the folder you want.
From this step onwards, you have the list of all files, you can use only one of the methods below to achieve the final results. The methods are listed from Easy to Complex. If you are not a Power Query expert, I suggest the very first one.
Method 1: Combine Again
You can click on the Combine Files menu again, to build the combine results automatically:
If you use this method, it will create another folder for the new function, then you can delete the old function manually.
Method 2: Use the Existing Function
You can go to Add Columns tab and Invoke a Custom function
then select the function with the input from the Content column, and then finally expand it.
Method 3: Do it in M Script
The last method is the one to copy the script for getting data from the SharePoint folder and paste it before the script to combine them all in one query;
Schedule Refresh
No matter which of the three methods above you used. After combining the data using the new SharePoint.Contents, you can then publish the file to the service again, and this time, schedule it to refresh without a gateway.
You need to set up the credentials using Edit Credentials, and then OAuth2.
And finally,you can schedule it to refresh;
A manual refresh also shows you that your process worked correctly.
Video
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
Reza, thanks a lot for highlighting the difference between SharePoint.Files and SharePoint.Contents — this is extremely useful when working with OneDrive!
Hi Daniil,
great to see you here.
It is great that you liked it
Cheers
Reza
Hi Reza,
This is great and I’ve used this technique. I found a problem however when the files are under another user’s account, in the same domain of course, and even with permisssions set to allow reading.
The problem I had was the list of files had an error row at the end. I tried filtering out errors but the error row always stayed there and prevented the results being used for anybfurther processing. I logged this in Power BI community but never had a resolution.
I ended up having to get each file individually which was a real pain.
Cheers, Rod
Hi Rod,
Strange, I haven’t tried it this way, but I guess it should be supported.
Cheers
Reza
An other option is to use Excel as a data source directly.
in the GetDATA choose Excel and point to the Excel file stored in Onedrive4business
Source = Excel.Workbook(Web.Contents(“https://yourcompany-my.sharepoint.com/personal/your_user_com/Documents/yourfilename.xlsx), null, true)
This method also works in Data Flows and Scheduled Refresh without a data gateway.
I must say that whatever method you use, the performance is very poor when loading data from Onedrive or Sharepoint 🙁
Yes, I explained the method of reading one file from OneDrive here.
Cheers
Reza
Hello,
Thanks for technique, very useful.
Some Excel files give error because formating is wrong. Anyway to filter out these records or to avoid having errors?
Thanks
Stéphane
If you have files with different formats, I suggest putting them in different folders, and just get data from same files in a folder.
you can filter based on name, extension etc. but filtering based on the structure is not easy. You need some extra steps such as finding out the columns in each file comparing it with a default template etc.
Cheers
Reza
Thank you so much! You’ve solved a problem MS hasn’t found a solution for.
Very handy, finally can combine folder and publish to web!
Thanks 😊
hello,when i was trying this,it appeared error:
AADSTS700027: Client assertion contains an invalid signature. [Reason – The key was not found., Thumbprint of key used by client:
y?
maybe an authentication issue? remove the connection and add it again to see what happens.
Cheers
Reza
Is it necessary to use sharepoint? I am using one drive only.
OneDrive for business and SharePoint online are pretty much the same thing
Cheers
Reza
Hi, I am confused with sharepoint URL. Actually I am not using sharepoint. I have one drive account having one folder called “Test”. There are plenty of excel files present in that folder with date extension.I want to connect few specific files on the basis of name (might be regex or other techniqe could be used) from power Bi.
Anyone please help me . I am in trouble. Thx in advance.
Are you using OneDrive personal or OneDrive for business?
Hi Reza,
Thank you for posting very useful techniques! Very well done!
My dashboard is connecting to multiple Excel files hosted by a single website but in different path of course.
For publishing, I have to do ‘Edit credentials’ for each of the data sources individually which is a pain.
Since all the data are hosted by a single website, it is more desirable to do ‘Edit credentials’ only once.
Is it possible please?
Thank you.
Hi Behnam
Depends on how the queries/tables/files are structures, you may be able to do it. you can have one query to get data from that website, and then other queries referencing from that and navigating to other areas.