How to simply connect Power BI to Excel file stored on SharePoint
This article describes, how to simply use a xlsx file on SharePoint as a datasource for Power BI.
Why should you connect Power BI to an Excel file on SharePoint?
If you want to use Excel as a datasource for Power BI, it is not that easy.
If the file is on your computer, Power BI will only reach it when you’re connected, and you also need a Gateway installed on your computer.
It’s possible to use a shared disk or server, but if your company has at least one SharePoint Online account, it’s probably easier to have the file in the SharePoint document library. There is no need to install the Gateway and the data is allways available in the SharePoint cloud – accessible for the powerbi.com cloud.
How to connect
One option is to go through Get Data / SharePoint Folder / Combine and Edit / …
This works, but it creates a complicated structure of queries with a custom function. In addition to the complexity, it has the disadvantage that custom features may not work properly when updating in the powerbo.com cloud, even if they work on Desktop.
It is much better to write a short code in M, and the connection is simple, clear and functional. For your purposes, of course, replace the names and addresses – marked in red.
let
Source = SharePoint.Files(“https://vyukaexcelu.sharepoint.com/“, [ApiVersion = 15]),
Filter = Table.SelectRows(Source, each ([Name] = “sample.xlsx”)), File = Filter{[Name=”sample.xlsx”,
#”Folder Path”=”https://vyukaexcelu.sharepoint.com/shared_files/”]}[Content],
#”Imported Excel” = Excel.Workbook(File),
#”Sheet or table selection” = #”Imported Excel”{[Item=”Sheet1“,Kind=”Sheet”]}[Data],
#”Promoted Headers” = Table.PromoteHeaders(#”Sheet or table selection”, [PromoteAllScalars=true])
in
#”Promoted Headers”
This is an awesome article.
I was trying to read from sharepoint excel and was really annoyed at the mess it creates if you follow the default path.
I have used your snippet. Works beautifully.
Thank you…:)
Hi,
Useful post.
Can we open the file with information right management/password protected?
Thanks
Hi
If I want to get multiple csv files from one SharePoint folder?
thank you this works!
I got the following error:
Expression.Error: The key didn’t match any rows in the table.
Details:
Key=Record
Table=Table
thank you so much! i lost a lot of time trying to get excel data from sharepoint folder in pbi