Create Power BI Reports from a SharePoint Online Library

By:   |   Comments (1)   |   Related: > SharePoint


Problem

SharePoint Online stores record information in a repository known as SharePoint Lists which are like SQL Server tables. SharePoint also allows users to store documents in a repository known as a "Library".  A Library can store different kinds of documents like Microsoft Office files, PDFs and others. It has the capacity to store millions of documents, but a challenge is how to use the information to create interactive and visual comparisons with SharePoint Online.

Solution

With the evolution of Power BI, Microsoft provides Power BI as a business analytics service. Microsoft also provides integration of Power BI with SharePoint Online. Power BI allows developers to use a SharePoint Library as a data source and generate interactive reports via Power BI online. It can then further be embedded in SharePoint Online. In this tip, we will show the steps to perform this process.

Before we move forward, I suggest going through this previous tip on "Deploying Power BI Reports to SharePoint Online". We are going to use a similar procedure here.

Let's start the process:

I have uploaded a sample file "Financial Sample.xlsx" in the folder "FinanceDocs". So, the final path of my library is "FinanceDocuments > FinanceDocs > Financial Sample.xlsx".

Sample date file in SharePoint Online

Here is a sample of the data:

Sample file with schema and data

Open Power BI desktop, let's create a data source so we can use it to generate charts. We need to select "SharePoint Folder" as a data source which is available under All as shown below. “SharePoint Folder” will provide all the files available at the specified data source in a flat hierarchy. Let’s select our data source. To do so, click on the "Home Tab > Get Data > More > All" and a similar screen as shown below should appear.

Select "SharePoint folder" and click "Connect".

Select data source type "SharePoint folder"

A screen will appear to complete the SharePoint Site information. Enter the site name and click "OK".

Enter site url

A screen with all the available documents will be shown. Click "Edit" and another window will open.

List of data sources

This is the window that opens after clicking edit.

List of all files available in site ur.

This window shows all the documents with its metadata. For this tip we will concentrate only on one file, thus we will select "Financial Sample.xlsx" which is the last file on the screen. The first column is the type of content. In our case, it is binary. Click on the link “Binary”.

Select and click on Binary

This opens the data that is available, in our case it is a table “financials” as well as a sheet “Sheet 1”.  You can see there are other links under the "Data" column which states the type of data i.e. "Table". Click on "Table" for the financials row.

Select and click on "Table" in data column

This will open the data as shown below.

Sample data preview

Now let’s create a connection, pull the data and start creating reports. In the above screenshot, you will be able to see "Home Tab > Close & Apply". Click on that option and you will be redirected to the home screen with an established connection.

Click on "Close & Apply"

As shown below, we will be able to see visualizations and fields.

Data source connected.

Now you can follow steps 7-17 from this article "Deploying Power BI Reports to SharePoint Online" to create a Power BI report in SharePoint Online.

Next Steps
  • Integrate multiple data sources from SharePoint Online and generate powerful reports in Power BI.
  • Check out these other Power BI tips.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Rahul Mehta Rahul Mehta is a Project Architect/Lead working at Tata Consultancy Services focusing on ECM.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Friday, April 16, 2021 - 1:23:08 AM - Varun Back To Top (88550)
Hi Rahul,
While attempting the steps, I get an error "Unable to Connect
We encountered a error while trying to connect
Details: Microsoft.Mashup.Engine1.Library.Resources.HttpResource.RequestFailed
OData version 3 and 4, Error: The remote server returned an error (404) not found (Not found)"














get free sql tips
agree to terms