Power BI: How to access an Excel file in a SharePoint Document Library

Developing a report or a dashboard using PowerBI with an Excel spreadsheet as a data source is simple and intuitive. Just a few clicks, and the data is available. But what if that Excel is in a SharePoint Document Library?

I faced this challenge, and as finding a solution took some time, I decided to share with you the experience and perhaps, help those who may be going through the same problem.

Speaking of the data source, an Excel file located on SharePoint usually has an address in the following format: https://<site>.sharepoint.com/sites/<directory>/<subdirectories>/.../<spreadsheet>.xlsx, and it is obtained when we access it using the browser.

Interestingly, in Power BI Desktop it is not possible to directly access a file using this address. The process involves making a connection, locating the file, and then opening and handling it.

To do this, the way is to create a new data source as a SharePoint folder, put the root address of SharePoint, credentials and select the desired file from the list that is presented.

This step-by-step is presented in several posts on the Internet, and my goal is to provide a shortcut. In large environments, as in my case, the list of files is huge. There are thousands of files, and most likely, finding the desired one will involve filtering the list and conducting a search.

The problem with this procedure is that they generate a series of undesirable steps in Power BI that will be executed every time you update your data.

The solution to this is to have a code that allows you to quickly inform Power BI where the data file is directly, and that is what you will find below:

let

?Source = SharePoint.Files("https://<site>.sharepoint.com/sites/<Root Directory>/", [ApiVersion = 15]),

?#"ExcelFile" = #"Source"{[Name="<EXCEL NAME>.xlsx",#"Folder Path"="https://<site>.sharepoint.com/sites/<Root Directory>/Shared documents/<subdirectory>/<subdirectory>/.../"]}[Content],

?#"Imported Excel Workbook" = Excel.Workbook(#"ExcelFile"),

?#"Sheet" = Table.SelectRows(#"Imported Excel Workbook", each [Kind] = "Sheet"){0}[Data],

?#"Promoted Headers" = Table.PromoteHeaders(#"Sheet", [PromoteAllScalars=true])

in

?#"Promoted Headers"

To use it, you must create a "Blank Query", and with it selected, open the "Advanced Editor" and copy and paste the code above, filling in the parts between "<>" with the data from your own environment.

In the line that specifies the "Sheet" reference, the {0} parameter specifies the number of the tab you want starting from "0". So check which Excel tab you want to read, in case there is more than one.

This is particularly useful because, depending on the version or language, the tab can take on several names. I have files with "Sheet 1" and "Page 1", for example. Also, the user can always change the name of the tab, and that would cause problems.

When you press "Done" you will need to provide your SharePoint credentials and the steps will then be added in "Applied Steps" and the content of the selected spreadsheet will be presented.

From there you will have the data available to perform the operations you are used to performing.

I hope this article helps you and if you have any questions, please post in the comments and I will clarify.

UPDATE JUNE/2023

I went to use my own instructions and noticed a change in behavior to get the Excel file address, which I will explain here.

The use of the original code caused a file not found error, which I found quite strange. After analysis and tests, I noticed that, although the directory seemed correct, SharePoint was adding an "invisible level":

Visible Address:

"https://<site>.sharepoint.com/sites/<root directory>/directory/<Excel file>.xlsx"

SharePoint Address:

"https://<site>.sharepoint.com/sites/<root directory>/Shared Documents/<directory>/<Excel file.xlsx"

This subdirectory, "Shared Documents", is absent when you open the Document Library in SharePoint. It is added when you copy the address of a file following these steps:

1. Click on the "..." on the right side of the file name;

2. Select "Details";

3. In the tab that opens on the right, click the "Copy" button next to the file path indication.

I corrected the code above, and for now, it is fine.

My SharePoint is in English. It will be important to check the behavior in Portuguese SharePoints.