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

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 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 12 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, Power BI Summit, and Data Insight Summit.
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.

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

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

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

Leave a Reply