Results 1 to 6 of 6

Thread: Acess data in shared onedrive file

  1. #1
    Seeker samdthompson's Avatar
    Join Date
    Aug 2018
    Posts
    5
    Articles
    0
    Excel Version
    365

    Acess data in shared onedrive file



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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:

    let
    Source = SharePoint.Files("https://XXX-my.sharepoint.com/personal/ZZZ_co_nz/", [ApiVersion = "Auto"])
    in
    Source

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,387
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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".
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Seeker samdthompson's Avatar
    Join Date
    Aug 2018
    Posts
    5
    Articles
    0
    Excel Version
    365
    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.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,387
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Seeker samdthompson's Avatar
    Join Date
    Aug 2018
    Posts
    5
    Articles
    0
    Excel Version
    365
    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

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,387
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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!
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •