Acess data in shared onedrive file

[samdthompson]

Hello, there lots of posts about accessing onedrive files and I can do that fine. I am running into problems with using an xlsx which is shared with me as a data source for PQ. When I access the sharepoint online list it show me all the files except for ones shared with me. I can open and edit the file that's shared with me fine so I assume that its something to do with permissions and that there is something extra I need to do in my PQ. The query I have to get at my sharepoint is:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]let
Source = SharePoint.Files("https://XXX-my.sharepoint.com/personal/ZZZ_co_nz/", [ApiVersion = "Auto"])
in
Source[/FONT]

 

[Ken Puls]

Hi Sam,

When you open the file, what gets returned if you go to File -> Info -> Copy Path?

Paste that in a cell. I can't say that I've tried to get to a Shared file through Power Query, so it would be interesting to see if the path is different from a file that you "own".

 

[samdthompson]

Yip, the file path is different. It shows the owners email rather than mine.

I have also tried accessing as web URL, plain excel file but putting in the URL for it.

Seems to me its a bit of a weakness on the the part of MS if its not a thing that can be done, given that its shared data.

Gave it a try via PowerBI, also failed.

 

[Ken Puls]

Damn. I suspect that we're stuffed here. With using the owner's email, that means that you would need to provide their credentials to connect to that folder. Even worse, (as if that's not bad enough,) you can only maintain one set of credentials in Power Query, so it's either yours or theirs.

I'll send this on to the Power Query team. I'd agree that it's a weakness, but honestly, then entire SharePoint connector is a pretty massive weakness. In my opinion that connector needs a full re-write from the ground up.

 

[samdthompson]

I am going to spend an afternoon tinkering with creating a flow so see if there is something I can do from that end. Silver lining, its nice to find something that is a genuine problem. Thanks for the input

 

[Ken Puls]

If you're successful, please post back with the method. I'd love to have a workaround for this kind of issue.

The same problems happen with OneDrive personal, as there is no connector available at all. Absolutely frustrating!