Power BI Get Data from Multiple Files in a Folder on OneDrive for Business, No Gateway Needed

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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:

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

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

12 thoughts on “Power BI Get Data from Multiple Files in a Folder on OneDrive for Business, No Gateway Needed

  • 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

  • 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 🙁

  • 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!

  • 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?

Leave a Reply

%d bloggers like this: