Forum Discussion

bpearce's avatar
bpearce
Advocate I
10 years ago

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 f...
Need Help
AntonRozenson's avatar
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))

 

 

bpearce's avatar
bpearce
Advocate I
9 years ago

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 "[email protected]"):     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...