Reporting Using Power BI With SharePoint As Data Source

In this article, we will see how to use Power BI desktop application to build a report.

Introduction

 
Power BI is a service/tool provided by Microsoft for visualizing and analyzing data from a wide range of data sources. You can easily build BI charts using two different platforms of Power BI.
  1. Power BI Web Service - here is a link to log in with your Office 365 account.
  2. Power BI Desktop Application - here is a link to download the latest version. One thing to note here, you don’t need a server to install Power Bi Desktop; you can install it on your Laptop device.
In this article, we will see how to connect to a SharePoint online list and create some basic charts with Power BI Desktop Application.
 

Connecting to SharePoint Online List with Power BI desktop app

 
Let’s jump into the steps directly.
 
Step 1
 
Open the Power BI desktop application from your laptop.
 
Reporting Using Power BI With SharePoint As Data Source
 
Step 2
 
Click on Get Data >> Click on More.
 
Reporting Using Power BI With SharePoint As Data Source
 
Step 3
 
As you can see below, you can connect to tons of data sources, any kind of database, Excel file, online services, custom API, public web service, almost anything.
 
Reporting Using Power BI With SharePoint As Data Source
 
Step 4
 
Search for SharePoint Online List >> Select SP Online List option and click Connect.
 
Reporting Using Power BI With SharePoint As Data Source
 
Step 5
 
Enter SP online Site URL and click on OK.
 
Reporting Using Power BI With SharePoint As Data Source
 
Step 6
 
Select to login with your Microsoft Account (office 365 account) >> it will prompt you to enter the details.
 
Reporting Using Power BI With SharePoint As Data Source
 
Step 7
 
Once login is done, a window like below will appear. All the lists will be populated from the entered site >> Select required list >> Click on Edit, as we need to modify the schema of the dataset:
 
Reporting Using Power BI With SharePoint As Data Source
 
Step 8
 
When clicked on Edit, the below Power Query Editor window will open, with this editor you can do the below things,
  1. You can specify which columns you need in your dataset
  2. Data formatting, if needed. Like cleaning some text fields, trimming of spaces, splitting some columns, date formatting etc.
  3. Adding a custom calculated column and many other things
Reporting Using Power BI With SharePoint As Data Source
 
Step 9
 
Select Choose Columns option from the tab menu.
 
Reporting Using Power BI With SharePoint As Data Source
 
Step10
 
Select required columns from the SharePoint list, it populates all systems, and hidden columns as well which you might not need for your reports. It is advised to remove such columns from the data set.
 
Reporting Using Power BI With SharePoint As Data Source
 
Step11
 
For Author/Editor columns you need to dig into it more to get actual values of the Created By, Modified by Users >> as shown below click on the split menu
 
Reporting Using Power BI With SharePoint As Data Source
 
Step12
 
Once your data set is ready now click on “Close & Apply”
 
Reporting Using Power BI With SharePoint As Data Source
 
Step13
 
You will see a screen like below. On the right side, you have VISUALIZATIONS controls, a dataset with selected FIELDS and the white space is a PAGE where you can add charts.
 
Reporting Using Power BI With SharePoint As Data Source
 
Step14
 
From the Visualization section, you can choose the type of chart/control you want. It has the below chart controls – Column, Bar, Pie, Donut, Line, Stacked, Table, Cards, Map, Slicer, Matrix, Guage, KPI and many more.
 
From the FIELDS section, you can choose the data for your charts as x-axis, y-axis, legends, tooltip, filters, etc.
 
Reporting Using Power BI With SharePoint As Data Source
 
Step15
 
Let’s add a simple pie chart with Status as Legend and Title column in Values. This chart shows the number of items per status.
You can save this project to the local folder as .pbix file. You can share this file to some other user. You can publish this report to an online Power BI service also.
 
Reporting Using Power BI With SharePoint As Data Source
 
That’s it for this article. Will see more details of Power BI in upcoming articles.