cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bpearce
Frequent Visitor

Need to load all Excel files in a given OneDrive folder...

Using Power BI Desktop, I can readily load to my data model all the Excel files in a given folder. Works great. However, I can't figure out how to accomplish the same if the files are in a OneDrive for Business folder. I can load a single Excel file from OneDrive when I fully specify the URL in the Get Data from Web function - but how do you get a list of files found in a OneDrive folder?

10 REPLIES 10
yossifisch
Advocate I
Advocate I

For OneDrive for Business or SharePoint, you can edit the query from SharePoint.Files to SharePoint.Contents. This will allow you to navigate to a specific folder by clicking the folder name to expand the table (which is the folder's contents) rather than filtering for it as I suggested in my previous comment. You can then use the Combine Files button to merge the files using the automated custom function just like you would when connecting to a local folder.


This solution is really fast. I found out about this from commenter Shawn on Excelerator BI: https://exceleratorbi.com.au/consolidate-multiple-excel-files-in-sharepoint-using-powerquery/#commen....

Not sure how this scales, both the file size and number of files I am using is pretty limited, but this works for me:

  • In Power BI Desktop go to Edit Queries and select the Source step. The function should be looking like SharePoint.Files("<your_url>", [ApiVersion = <version>])
  • Use this function as first parameter to the Table.FindText function, and use the second parameter for your file/folder pattern. So the new function in the Source step should looks something like  Table.FindText(SharePoint.Files("<your_url>", [ApiVersion = <version>]), "<your_filter>")
  • When adding a new source (e.g. if you want some other files with a different data model in a separate folder to be included), make sure you Transform Data first if needed, as you need to use the two steps above to filter out the correct files/folders. After that, the two queries will have their own helper queries and can have their own file transformations.
Greg_Deckler
Super User
Super User

Other than syncing your OneDrive folders locally, I can't think of a way to do this, I don't think you can pass a Folder URL to the "Folder" data source.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

"Other than syncing your OneDrive folders locally..."

Sorry, I should have further specified it is critical that when published, Power BI must be able to refresh the data model. Thus specifying a local folder as a data source will not work.

 

Any other suggestions?

 Solved:

 

 1. Get a list of files (URLs) via = SharePoint.Tables("https://SomeOrgName.sharepoint.com/personal/Email_with_undescores/", [ApiVersion = 15])

 2. Expand Documents "Table"

 3. Expand "File.LinkingUrl" and split by "?" to get a clean URL

 4. Do filter the desired folder (via URL or Folder.Name or other attribution)

 5. Add a Custom Column = Excel.Workbook(Web.Contents([File.LinkingUrl))

 

 

Update 5/29/19: Check my newest comment for a much faster solution: https://community.powerbi.com/t5/Integrations-with-Files-and/Need-to-load-all-Excel-files-in-a-given...

 

Original 1/10/2018: 

Awesome solution, a small typo correction though:

You were missing a closing bracket "]" (in red below).

 



 

 5. Add a Custom Column = Excel.Workbook(Web.Contents([File.LinkingUrl]))

 

 


 

Edit 1/10/2018: I thought it would be an awesome solution when I saw it beginning to load (see my original post). However, when it finished loading, my custom column returned got this error:

 

 

Expression.Error: We cannot apply field access to the type Function.
Details:
    Value=Function
    Key=File.LinkingUrl

 

 Edit 1/11/2018: Since this is the only solution available (all other similar forums close with "vote up feature request" responses), I was trying hard to get this to work and after concocting solutions from all over, I realized that I was simply missing the word "each" from the custom column.

I should point out that I am a beginner at Power Query and now that I realized that @AntonRozenson's solution was simply missing the word that makes it loop for each column, I suspect this would be very obvious for a seasoned user.

 

So here is the final solution using @AntonRozenson's suggestion as a base and applying @bpearce's and my corrections:

 

Step 1 - Get a list of all document URLs:

a. New Query > Online Services > SharePoint Online List

b. Paste in your OneDrive root folder URL up to and including the email address: https://SomeOrgName-my.sharepoint.com/personal/Email_with_undescores/

c. From the list of tables that appear, choose the "Documents" table

d. Expand the "File" Column, the only field you need is "LinkingUrl"

 

Step 2 - Target documents in a specific folder (if that's required):

a. Convert column "LinkinUrl" to type Text (this will enable "Begins With..." filtering)

b. Click the dropdown on column "LinkinUrl" > Text Filters > Begins With...

c. Paste in the folder path: It starts with the root folder URL used in step 1 b, then Documents/ and then Folder/Sub Folder/etc/ (you can choose one from the dropdown and remove the extra parts)

d. OK

 

Step 3 - Clean up the URL:

a. Split Column > By Delimiter > Select --Custom-- > enter ? > Split at Right-most delimiter

 

Step 4 - Get the files associated with the URLs:

a. Add Column > Custom Column

2. In the Cusomn column formula box, enter: each Excel.Workbook(Web.Contents([LinkingUrl])) (If you are working with a folder that has CSV files, you can replace "Excel.Workbook" with "Csv.Document") - Note that your column may have been renamed during the Expand Column or Split Column steps. You can either rename the column or substitute the new name within those brackets (e.g. "each Excel.Workbook(Web.Contents([File.LinkingUrl.1]))" )

 

That's it!

 

This solution is not very fast but it works and is the only solution I could find.

 

If you need to do transformations to the files before merging them, you can create a custom function that imports the file and applies those trasformations. In such case the URL should be a parameter to that function so the custom column will look something like: each myCustomFunction([LinkingUrl]). This video can help.


Step 2 - Target documents in a specific folder (if that's required):

a. Convert column "LinkinUrl" to type Text (this will enable "Begins With..." filtering)

b. Click the dropdown on column "LinkinUrl" > Text Filters > Begins With...

c. Paste in the folder path: It starts with the root folder URL used in step 1 b, then Documents/ and then Folder/Sub Folder/etc/ (you can choose one from the dropdown and remove the extra parts)

d. OK

 


This filtering step is excruciating slow, for a folder of 10 files. It looks like the entire "Sharepoint Online List" retrieved is huge and full of irrelevant records.

 

Any idea how to make it faster ?

Anonymous
Not applicable

Thanks for all the help, this worked for me. 

But, I am not able to refresh the data on the reports , the 'refresh now' and 'scheduled refresh' doesnt seem to work. The same is mentioned in the below link

https://docs.microsoft.com/en-us/power-bi/refresh-csv-file-onedrive 

 

The link also suggests that the data will be automatically sync'ed every hour or so , but its not happening in my case. Can some one please help ?

Hello @yossifisch,

 

In the last step, when I try to add the custom colum I face the error "Access to the resource is forbidden".

 

Do you have any ideas on how to solve this?

 

Thanks,

Ugur

Thanks for the guidance Anton as this is still a huge issue for us. I was able to make headway with steps 1 & 2, but when I go to expand [File] or even [Folder] fields it hangs indefinately. I'll try again later in case the Msft Cloud squirrels are napping... but glad to hear this may work...

 

For those watching... here is what I did:

1a) brought up OneDrive in a browser so I could copy the URL. Cleaned it up per Anton's suggestions. Note that I did need to specify "SomeOrgName" followed by "-my" in order for it to work.

1b) Started a Blank Query then in Advanced Editor specified (assume company "acme" with  email "john.doe@acme.com"):     Source = SharePoint.Tables("https://acme-my.sharepoint.com/personal/john_doe_acme_com/",[ApiVersion=15]),

1c) on exit from the editor I was prompted to edit credentials & on doing so was presented a table of objects

2) was able to expand the Documents Table

3) was not able to expand [File] or [Folder] columns... timeouts for both...

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors