Accessing Excel File Stored On SharePoint Site From Microsoft Power BI

  • Published
  • Posted in Power BI
  • Updated
  • 2 mins read
  • Tagged as

In this blog post, one can learn how to generate a Power BI report using data stored on a client’s SharePoint Site.

Let’s starts with understanding what is SharePoint?

“Microsoft SharePoint is a cloud-based service that helps organizations share and manage content, knowledge, and applications” – Microsoft Documentation.

In this post, we are using Excel files that are stored on SharePoint. The assumption is that the excel files have similar structure because in Power BI they are combined into a single table.


Firstly, one has to open Power BI desktop, click the “Get Data” button, select “More” and finally select “SharePoint Folder”.

Get Data

Next, after selecting “SharePoint Folder”, one has to “Connect” upon which one would see a prompt to input URL of the SharePoint document library.

Power BI expects root URL that points to the path to SharePoint Site

For instance, for the complete SharePoint URL

https://[partner][Your Site Name]/SitePages/Home.aspx

Corresponding Power BI Dashboard URL would be following:

https://[partner][Your Site Name]/

Power BI

Next, input the SharePoint credentials to authenticate the connection to your SharePoint Site.

Finally, one can see list of all the stored files in the corresponding SharePoint site folder.

Now, that we see all the files, let us see how to process the data in the files. We could directly combine the data of files by clicking on


“Combine” but since we may have files in this library that we don’t need, we’ll first filter the required files by clicking on the “Transform Data”. This will take you to Power Query Editor where one can filter the required file(s).

Power Query Editor

For instance, I’ve applied filter on “Name” column as I want to see data from “Sales – April 2020.xlsx”, “Sales – May 2020.xlsx” & “Sales – June 2020.xlsx” to load into Power BI Service. After applying filter, click on “Binary” to fetch data from these files.

combine files

Now, one is ready to combine data from all the required files. Select the files one by one from the drop-down list and select the files from parameter folder below.

After selecting the required files, now the data is ready to load into Power BI Financial Reporting and generate reports.

power bi

This is all about loading data from multiple excel files stored on SharePoint Site to Power BI. I hope it gives the information needed.

Addend Analytics is a Microsoft Gold Partner based in Mumbai, India, and a branch office in the U.S.

Addend has successfully implemented 100+ Microsoft Power BI and Business Central projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia.

Get a free consultation now by emailing us or contacting us.