Accessing Excel file Stored on SharePoint Site from Microsoft Power BI

This is my first ever blog. In this blog post, one can understand how to generate a Power BI report using data stored on client's SharePoint site.

Let's starts with understanding, what is SharePoint ?

"Microsoft SharePoint is a cloud-based service that helps organizations to share and manage content, knowledge and application." - Microsoft Documentation

In this post, we are using excel files that are stored on SharePoint and combining these files into one dataset in Power BI. The assumption is that the excel files have similar structure because Power BI will apply same operations (i.e. transformation, aggregation etc) on all of these files.

SharePoint Site

Above is my sample SharePoint site with three files in it.

Now, Lets start with opening Power BI Desktop and then click on "Get Data" button. Now you will see a prompt showing list of various data connectors available in Power BI. Now click on "More" or you can search it by typing connector's name on search box. Now finally select "SharePoint Folder".

Power BI Connectors

Above is the list of various data connectors available with Power BI.

Next, after selecting the "SharePoint Folder" connector, click on "Connect" upon which you would see a prompt asking for 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].onmicrosoft.com/sites/[Your Site Name]/SitePages/Home.aspx

Corresponding Power BI URL would be following - 

https://[partner].onmicrosoft.com/sites/[Your Site Name]/

Enter URL

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

Now, after authentication step you will see list of all the stored files in the corresponding SharePoint site folder.

List of SharePoint Files 

Now, that we see all the files, let us see how to process the data in the files. We could directly the data of files by clicking "Combine" but we may have files in this library that we don't need. So, we will first filter the required files by clicking the "Transform Data". This will take you to the Query Editor where you can filter the required file(s).

Filtered Files

For instance, I've applied the filter on "Name" column as I want to load the data  from "Sales-April2020.xlsx", "Sales-May2020.xlsx" and "Sales-June2020.xlsx" into Power BI.

After applying the filter, click "Binary" to fetch the data from these files.

Combining Data

Now, you are ready to combine the data from all the required files. Select the files one by one from the dropdown list and select the files from the parameter folder below as well.

After selecting the data from required files, now your data is ready to load into Power BI and generate report.

Combined Records

Now you can click on "Close & Apply" and you will be back to Power BI Desktop.

This is all about loading data from multiple excel files stored on SharePoint to Power BI.

Thank you for reading this blog, I hope it gives the information needed.