By: Rahul Mehta | 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:
- The first step is to create a data source in SharePoint Online Library. For that I suggest creating a SharePoint Library and a folder inside a Library. For those who don't know how to create, please review these links below:
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](/tipimages2/5368_reporting-in-sharepoint-online-using-power-bi.001.png)
Here is a sample of the data:
![Sample file with schema and data](/tipimages2/5368_reporting-in-sharepoint-online-using-power-bi.002.png)
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"](/tipimages2/5368_reporting-in-sharepoint-online-using-power-bi.003.png)
A screen will appear to complete the SharePoint Site information. Enter the site name and click "OK".
![Enter site url](/tipimages2/5368_reporting-in-sharepoint-online-using-power-bi.004.png)
A screen with all the available documents will be shown. Click "Edit" and another window will open.
![List of data sources](/tipimages2/5368_reporting-in-sharepoint-online-using-power-bi.005.png)
This is the window that opens after clicking edit.
![List of all files available in site ur.](/tipimages2/5368_reporting-in-sharepoint-online-using-power-bi.006.png)
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](/tipimages2/5368_reporting-in-sharepoint-online-using-power-bi.007.png)
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](/tipimages2/5368_reporting-in-sharepoint-online-using-power-bi.008.png)
This will open the data as shown below.
![Sample data preview](/tipimages2/5368_reporting-in-sharepoint-online-using-power-bi.009.png)
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"](/tipimages2/5368_reporting-in-sharepoint-online-using-power-bi.010.png)
As shown below, we will be able to see visualizations and fields.
![Data source connected.](/tipimages2/5368_reporting-in-sharepoint-online-using-power-bi.011.png)
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.
About the author
![MSSQLTips author Rahul Mehta](/images/Rahul-Mehta.jpg)
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips