Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

FREE Webcast - Delivering Microsoft SQL Server High Availability on AWS
 

Create Power BI Reports from a SharePoint Online Library


By:   |   Read Comments   |   Related Tips: > 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.


Last Update:


next webcast button


next tip button



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

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools