Forum Discussion
Get a SharePoint file path to use in PBI without the Desktop app
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
Super UserBy "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.