Reporting Using Power BI With SharePoint As Data Source

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.

 

 

Step 2

 

Click on Get Data >> Click on More.

 

 

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.

 

 

Step 4

 

Search for SharePoint Online List >> Select SP Online List option and click Connect.

 

 

Step 5

 

Enter SP online Site URL and click on OK.

 

 

Step 6

 

Select to login with your Microsoft Account (office 365 account) >> it will prompt you to enter the details.

 

 

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:

 

 

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

 

Step 9

 

Select Choose Columns option from the tab menu.

 

 

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.

 

 

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

 

 

Step12

 

Once your data set is ready now click on “Close & Apply”

 

 

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.

 

 

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.

 

 

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.

 

 

That’s it for this article. Will see more details of Power BI in upcoming articles.