Connect Onedrive Excel files to Power Query 2016

Steel Contributor
I have problem to connect Excel files in One drive to Power Query. I have doubt is the Onedrive allow this type of connection? Please share. Thanks
4 Replies
Yes, it allows. Could be like Source = Excel.Workbook(Web.Contents("https://contoso-my.sharepoint.com/personal/contoso/Documents/UpFolder/myFile.xlsx"), null, true)

I think this answers the question for OneDrive for Business, but what about personal OneDrive folders? The similar URL from the browser looks like (in my case):
https://onedrive.live.com/?WT.mc_id=A77%5FBingHP&id=root&cid=777AA7AAAA7AAA77

 

I guess the real question is does OneDrive (for Personal) keep some attributes for both tenant/subscription/individual statically or dynamically.

 

Thanks in advance,

David

Hi Sergei, is there a way to reference a shared folder on OneDrive for Business? The scenario is I have a report and a subfolder of workbooks saved in one of my ODB folders. The Query Connection was created in desktop Excel's Power Query using synced local files. Now I'd like to give a colleague access to the report and source data via ODB. Assuming I've given the colleague a link to the folder, can I modify the Query to point to the corresponding source on ODB? Or is there a better way to do this? Thanks!

Hi Greg,

 

I din't work with shared folders on ODFB, we usually share on SPO and access looks like (if for Teams site)

Source = SharePoint.Files("https://contoso.sharepoint.com/teams/teamname", [ApiVersion = 15]),

Perhaps that's possible for ODBF as well, connection looks like

Source = SharePoint.Files("https://contoso.sharepoint.com/personal/username", [ApiVersion = 15]),

where you may filter needed folder. The point is you shall pick-up username somehow and combine it with the link, not sure how to do that.