Connect Onedrive Excel files to Power Query 2016

%3CLINGO-SUB%20id%3D%22lingo-sub-205145%22%20slang%3D%22en-US%22%3ERe%3A%20Connect%20Onedrive%20Excel%20files%20to%20Power%20Query%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205145%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Greg%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20din't%20work%20with%20shared%20folders%20on%20ODFB%2C%20we%20usually%20share%20on%20SPO%20and%20access%20looks%20like%20(if%20for%20Teams%20site)%3C%2FP%3E%0A%3CPRE%3ESource%20%3D%20SharePoint.Files(%22https%3A%2F%2Fcontoso.sharepoint.com%2Fteams%2Fteamname%22%2C%20%5BApiVersion%20%3D%2015%5D)%2C%3C%2FPRE%3E%0A%3CP%3EPerhaps%20that's%20possible%20for%20ODBF%20as%20well%2C%20connection%20looks%20like%3C%2FP%3E%0A%3CPRE%3ESource%20%3D%20SharePoint.Files(%22https%3A%2F%2Fcontoso.sharepoint.com%2Fpersonal%2Fusername%22%2C%20%5BApiVersion%20%3D%2015%5D)%2C%3C%2FPRE%3E%0A%3CP%3Ewhere%20you%20may%20filter%20needed%20folder.%20The%20point%20is%20you%20shall%20pick-up%20username%20somehow%20and%20combine%20it%20with%20the%20link%2C%20not%20sure%20how%20to%20do%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-204023%22%20slang%3D%22en-US%22%3ERe%3A%20Connect%20Onedrive%20Excel%20files%20to%20Power%20Query%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-204023%22%20slang%3D%22en-US%22%3EHi%20Sergei%2C%20is%20there%20a%20way%20to%20reference%20a%20shared%20folder%20on%20OneDrive%20for%20Business%3F%20The%20scenario%20is%20I%20have%20a%20report%20and%20a%20subfolder%20of%20workbooks%20saved%20in%20one%20of%20my%20ODB%20folders.%20The%20Query%20Connection%20was%20created%20in%20desktop%20Excel's%20Power%20Query%20using%20synced%20local%20files.%20Now%20I'd%20like%20to%20give%20a%20colleague%20access%20to%20the%20report%20and%20source%20data%20via%20ODB.%20Assuming%20I've%20given%20the%20colleague%20a%20link%20to%20the%20folder%2C%20can%20I%20modify%20the%20Query%20to%20point%20to%20the%20corresponding%20source%20on%20ODB%3F%20Or%20is%20there%20a%20better%20way%20to%20do%20this%3F%20Thanks!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-164409%22%20slang%3D%22en-US%22%3ERe%3A%20Connect%20Onedrive%20Excel%20files%20to%20Power%20Query%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-164409%22%20slang%3D%22en-US%22%3E%3CP%3EI%20think%20this%20answers%20the%20question%20for%20OneDrive%20for%20Business%2C%20but%20what%20about%20personal%20OneDrive%20folders%3F%20The%20similar%20URL%20from%20the%20browser%20looks%20like%20(in%20my%20case)%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fonedrive.live.com%2F%3FWT.mc_id%3DA77%255FBingHP%26amp%3Bid%3Droot%26amp%3Bcid%3D777AA7AAAA7AAA77%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fonedrive.live.com%2F%3FWT.mc_id%3DA77%255FBingHP%26amp%3Bid%3Droot%26amp%3Bcid%3D777AA7AAAA7AAA77%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20guess%20the%20real%20question%20is%20does%20OneDrive%20(for%20Personal)%20keep%20some%20attributes%20for%20both%20tenant%2Fsubscription%2Findividual%20statically%20or%20dynamically.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%20in%20advance%2C%3C%2FP%3E%0A%3CP%3EDavid%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-31033%22%20slang%3D%22en-US%22%3ERe%3A%20Connect%20Onedrive%20Excel%20files%20to%20Power%20Query%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-31033%22%20slang%3D%22en-US%22%3EYes%2C%20it%20allows.%20Could%20be%20like%20Source%20%3D%20Excel.Workbook(Web.Contents(%22%3CA%20href%3D%22https%3A%2F%2Fcontoso-my.sharepoint.com%2Fpersonal%2Fcontoso%2FDocuments%2FUpFolder%2FmyFile.xlsx%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fcontoso-my.sharepoint.com%2Fpersonal%2Fcontoso%2FDocuments%2FUpFolder%2FmyFile.xlsx%3C%2FA%3E%22)%2C%20null%2C%20true)%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-30943%22%20slang%3D%22en-US%22%3EConnect%20Onedrive%20Excel%20files%20to%20Power%20Query%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-30943%22%20slang%3D%22en-US%22%3EI%20have%20problem%20to%20connect%20Excel%20files%20in%20One%20drive%20to%20Power%20Query.%20I%20have%20doubt%20is%20the%20Onedrive%20allow%20this%20type%20of%20connection%3F%20Please%20share.%20Thanks%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-30943%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional 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
Highlighted
Yes, it allows. Could be like Source = Excel.Workbook(Web.Contents("https://contoso-my.sharepoint.com/personal/contoso/Documents/UpFolder/myFile.xlsx"), null, true)
Highlighted

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

Highlighted
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!
Highlighted

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.