Forum Discussion
Need to load all Excel files in a given OneDrive folder...
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.
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.