Forum Discussion
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:
- Go to SharePoint (or OneDrive) and open the file in the DESKTOP app
- Click File, then Info
- click the "Copy Path" option to copy the link into the Clipboard
- Go to Power BI
- 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!
Hi Anonymous ,
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 Query or Power BI | CloudExtend Help Center
8 Replies
- ImkeF
Community Champion
Hi Anonymous ,
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 Query or Power BI | CloudExtend Help Center 
v-jingzhangCommunity Support
Hi Anonymous
Here is where you can find the direct link of files in SP Online and OneDrive Online.
Best Regards,
Community Support Team _ Jing- AlexisOlson
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_TableIf 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.
- it collects my site name
- the picks up the folder where the file is stored
- it recognizes the file name
- 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.
- Vijay_A_Verma
Most Valuable Professional
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
This link you can feed into Web connector of Power BI.
Saikumar_3721Microsoft Employee
what if the copy path link is also not working to load the data into the power bi desktop?
