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.
