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.
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:
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;
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;
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.