Power Query: Get Data from an online Excel workbook (OneDrive, SharePoint)

Question

I've asked this before, and I think I got some comments on the question not being specific/researched enough (I'm new here!) So I'm posting again, with a bit more research. Apologies if there is still anything missing:

I need an Excel workbook to get data from another Excel workbook that lives on OneDrive. I want to share the mirror workbook with other coworkers so they can reference it.

As I have it now, on my OneDrive folder in my computer I created the mirror workbook and got data from the master workbook (also in my OneDrive folder). Lastly, I have shared the mirror workbook though OneDrive with a coworker. However, when I test opening the workbook from the coworker's computer, the workbook is still referencing the file path as if it were in my computer where I created the query and the workbook files.

As I understand it, instead of a file path, the workbooks needs a URL to reference the master workbook that lives in OneDrive (which I don't want to share).

I've tried generating different share links from the OneDrive web portal but nothing seems to access. I get access denied messages.

A bit of background, everyone has their own 365 account. And I am doing everything from the app. Not using the browser version of Excel a ay point.

Alexis Olson Alexis Olson · Accepted Answer · 2021-01-25T21:07:35

I've done SharePoint before using the SharePoint folder data connector. To do this, select that connector option, paste in the site URL (e.g. https://company.sharepoint.com/sites/GroupName/), and use Microsoft account as the authentication method.

Example M query:

let
    Source = SharePoint.Files("https://company.sharepoint.com/sites/GroupName/", [ApiVersion = 15]),
    #"Excel File Name" = Source{[Name="Excel File Name.xlsx",#"Folder Path"="https://company.sharepoint.com/sites/GroupName/Shared Documents/General/"]}[Content],
    #"Imported Excel" = Excel.Workbook(#"Excel File Name"),
    #"Sheet Name_Sheet" = #"Imported Excel"{[Item="Sheet Name",Kind="Sheet"]}[Data]
in
    #"Sheet Name_Sheet"

Power Query: Get Data from an online Excel workbook (OneDrive, SharePoint)

1 Answers