Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
WillBeeSEA
Helper I
Helper I

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

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!

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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...

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

7 REPLIES 7
v-jingzhang
Community Support
Community Support

Hi @WillBeeSEA 

 

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

vjingzhang_0-1651915831388.png

 

Best Regards,
Community Support Team _ Jing

ImkeF
Super User
Super User

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...

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Vijay_A_Verma
Super User
Super User

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
Helper I
Helper I

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
Super User
Super User

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.

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?

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors