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?

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-OneDrive-folder/m-p/702472#M23521 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 Listb. 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" tabled. 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 Column2. 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.

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/#comment-287.

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))

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 objects2) was able to expand the Documents Table3) was not able to expand [File] or [Folder] columns... timeouts for both...

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.

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

11 Replies

yossifisch
Advocate I
7 years ago
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/#comment-287.
- Anonymous
  Not applicable
  6 years ago
  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
Community Champion
10 years ago
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.
- bpearce
  Advocate I
  10 years ago
  "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?
  - AntonRozenson
    Regular Visitor
    9 years ago
    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))

Forum Discussion

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

11 Replies

Related content

OneDrive Folder as source

Path to onedrive folder office 365 home

OneDrive Refresh

Change file path from folder to OneDrive

Merge data from multiple excel files from multiple sharepoint folders