Get a SharePoint file path to use in PBI without the Desktop app

[WillBeeSEA]

To connect to an Excel file stored in SharePoint Online (or OneDrive), the typical instructions are to:

1. Go to SharePoint (or OneDrive) and open the file in the DESKTOP app
2. Click File, then Info
3. click the "Copy Path" option to copy the link into the Clipboard
4. Go to Power BI
5. Get Data, choose Web and paste the link there (removing the last few characters "?web=1").  Perhaps enter credentials.

Ok that works fine.  BUT is there a way to get that path WITHOUT the Desktop app?  Suppose you are on a tablet or other device that does not have the Office Suite installed.  Is there no way to get that file path link without the Desktop app?  Seems like it must be in the file somewhere that could be accessed without the Desktop app.  Going to SharePoint Online (or OneDrive) clicking the "Get link" option there gives a different link and that does not work with Power BI.

Thank you!

[Saikumar_3721]

what if the copy path link is also not working to load the data into the power bi desktop?

[v-jingzhang]

Hi @WillBeeSEA 

Here is where you can find the direct link of files in SP Online and OneDrive Online. 

Best Regards,
Community Support Team _ Jing

[ImkeF · Accepted solution]

Hi @WillBeeSEA ,
you can retrieve that link from SP or OneDrive directly like described here:
Get Excel Data from a Single File or Entire Folder on SharePoint or OneDrive for Business into Power...

[Vijay_A_Verma]

Do like this (I am giving this step from personal Onedrive, hence should work for Sharepoint online)

Right click on your file - Embed

I got below code

<iframe src="https://onedrive.live.com/embed?cid=E11B26EEAACB7947&resid=E11B26EEAACB7947%217398&authkey=AA8hHIRW2IjGghI&em=2" width="402" height="346" frameborder="0" scrolling="no"></iframe>

Now copy the bold portion starting from resid till just before &em

Now, below is sample link where bold portion should be replaced with above copy.

https://onedrive.live.com/download?resid=AAXXXX&authkey=AAXXXXXXX&em=x&app=Excel

Hence, this would become

https://onedrive.live.com/download?resid=E11B26EEAACB7947%217398&authkey=AA8hHIRW2IjGghI&em=x&app=Ex...

This link you can feed into Web connector of Power BI.

[WillBeeSEA]

I was referring to needing the Excel Desktop app to get the "Copy path" link for using in Power BI Desktop to connect to an Excel file that is stored in SharePoint Online.

[AlexisOlson]

By "Desktop app", do you mean Power BI Desktop or Excel Desktop or SharePoint Desktop?

I usually load Excel files from SharePoint like this:

let
    SharePointSite = "https://organizationname.sharepoint.com/sites/SiteName",
    FolderPath = SharePointSite & "/Shared Documents/"
    FileName = "ExcelFile.xlsx",
    Source = SharePoint.Files(SharePointSite, [ApiVersion = 15]),
    ExcelFile = Excel.Workbook(Source{[Name=FileName, #"Folder Path"=FolderPath]}[Content]),
    Table2_Table = ExcelFile{[Item="Table2",Kind="Table"]}[Data]
in
    Table2_Table

If you know the folder and the file, just stick them in.

[WillBeeSEA]

Answering your question: I was referring to needing the Excel Desktop app to get the "Copy path" link for using in Power BI Desktop to connect to an Excel file that is stored in SharePoint Online.

I'm not sure about the second-to-last line of your code that you posted above.  When I click through the Applied Steps, the first lines seem fine.

1. it collects my site name
2. the picks up the folder where the file is stored
3. it recognizes the file name
4. the Source step lists all the contents of the site (as it should)

but now the ExcelFile line fails with the following message.  I replaced the DOMAN, SITE, and FILE with canned text here, but in the actual error message, the correct content is seen.  

----------------

Expression.Error: The key didn't match any rows in the table.
Details:
Key=
Name=EXCEL_FILE.xlsx
Folder Path=https://DOMAIN.sharepoint.com/sites/SITE/Shared Documents/
Table=[Table]

----------------

The "KEY" is blank, but I'm not sure what it is expecting.  Apparently a previous line is needed to define the key?

[AlexisOlson]

I tested this again and get exactly the error you mention if I leave off the "/" at the end of the FolderPath but works just fine when I put it back in. I'd recommend double-checking for small errors like that.

If you can't find any errors like that, remove the steps after Source and manually navigate by clicking on the Binary (from the [Content] column) and Table (from the subsequent [Data] column) to get to the table you want.