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”

---