Using Power BI for SharePoint Reporting

As a Data Scientist at Sharegate, I'm always looking out for better ways to visualize all sorts of useful data. A few months ago, I started using Power BI, a user-friendly data visualization and reporting tool created by Microsoft. Now I'm hooked!

In this article, I'll introduce this tool and will cover some reports that can easily complement Sharegate's built-in reports, so that you can gain a full visibility over your SharePoint environments.

I personally find it very useful to report on SharePoint folders and/or lists, because it's important to ensure full adoption of this enterprise tool.

At the end of the day, SharePoint is mostly a collaborative software, so basically it's as useful as the people who are using it on a daily basis. Sharegate and Power BI can ensure that your SharePoint environment is properly used by the end users, and this is what really counts.

First Things First, What is Power BI?

Power BI is a data visualization tool, which is available for free through your Microsoft Account. It's a relatively new tool that is available on Desktop version and Online. 

If you're familiar with reporting tools already, I can guarantee you that this is not like SSRS. It's much simpler to use. You can publish your dashboards and reports online with gorgeous user interfaces in just two clicks, and to any group you want.

Why you Should Try Power BI

It's free. Yes, there's a Pro version, which enables users to present more data, but you should be ok with the free version for most of your reports.
There are about 65 different sources of data available that can be integrated into Power BI. From an SQL database to 3rd party tools such as Salesforce, Google Analytics and other useful tools.
No coding is necessary. You can configure your data using simple operations and can also use Simili-Excel operations to gain more flexibility. These are called DAX basics operations.
With Microsoft Group workspaces and Office 365, sharing dashboards has never been easier. You can share your dashboards to only some individuals part of a group, and they can all access the online versions through your Microsoft Services page. Thanks to Office 365, you can also share the dashboards to guests.

Power BI's SharePoint Capabilities

Here's a good example as to why you may want to use Power BI in SharePoint.

At Sharegate, every employee goes through a rigorous training on how to properly use our tool. We are a software development company after all!

This is a dashboard I built to monitor the lessons created and updated in Sharegate's SharePoint team site. The goal of this is to make sure employees are constantly collaborating to keep the training material current.

Below are the instructions on how to produce a dashboard, using data from your SharePoint environment.

Power BI Dashboard Creation Process

Instructions

Download Power BI for Desktop

Although Power BI has two versions (Online and for Desktop), I suggest using the Desktop version to create the dashboard, then publish it through Power BI Online to share the dashboard with your colleagues.
Get data from SharePoint

When opening Power BI, click on the "Get Data" button to access the data sources that are available.
Select the right data type

As you will see, there are 3 types of available SharePoint data connectors; Folders, Online Lists and regular lists. Pick your object type and add your SharePoint URL.
Load your data

On the left site, select the relevant folder or list. You will see a preview of the data that can be loaded.
Edit your query to make good visuals 

Now that you've loaded your data, you can create graphs such as the ones below. Here are a few tricks in case you need to edit your query.

In order to create the right visuals, you may have to create columns, or group rows together. Microsoft explains well how this should be done.

Below is an example of a line chart. I have decided to create a line chart to show how many training lessons were updated, per month.
1. I have selected the line graph visual, located at the top of the visualization menu (which is one of the 2 gray columns beside the report section).
2. For the axis and values of the graph, simply drag and drop the data from the Fields column, which is essentially your data source fields.
3. To make the graph clearer, I grouped the dates into months by creating a new column. At the top, click the “New Column” button, after which a formula bar will appear. The formula below allows to create a new column in the dataset, which is the first day of the month from the “Modified Date” field.
  
  Month = STARTOFMONTH('Formation - SharePoint & Sharegate'[Modified Date])
4. If you want to filter out some values, you can do that from the Filter section of the Visualizations column.
5. Finally, you can customize the view by clicking on the paint roller icon.
Publish your report to Power BI Online

When you are satisfied with your visuals, publish the report to Power BI Online, by clicking on the "Publish" button. If you have not activated Power BI on https://login.microsoftonline.com, you will be asked to activate it.

You will then have the option of publishing your report in your workspace or in your Office group workspaces (if you have groups already created).

Tip: I suggest you create group workspaces if you plan on sharing your dashboard with colleagues. You can then create a group for quality assurance, and push the validated dashboards into your team group workspace, where you can then collaborate with your colleagues.
Find your published report on Power BI Online

Go on powerbi.microsoft.com. You will see the report in the gray section in the middle of the left column. Before we go further, here are the different elements visible in this section.

Workspace: This is either your personal workspace or a shared group workspace.

Dashboard: A dashboard is the high-level view of the report we created previously. You can transform your report by pinning reports tiles.

Reports: This is what is being published from Power BI desktop. To transform this report into a dashboard, you need to pin the tiles into a dashboard.

Datasets: This is the source data, to make the data dynamic, you need to go in this section and schedule automated refreshes.
Transform your report into a dashboard

It is quite straight forward, you only have to access your report and click on the pin icon. Then, you can select or create the dashboard you want your visual on.
Schedule an automated refresh

This is important, otherwise your dashboard will never refresh. To do that, go under the dataset section, find your dataset, click on the "..." button and select the scheduled refresh option. You will finally have the option to enter your credentials and select the refresh frequency.
Share your dashboard

You finally have a functional, automatically refreshed dashboard that can be easily shared! If you create a Group Workspace, you will need to give access to the workspace to your users. Then, you can ask them to connect to powerbi.microsoft.com or you can simply send the URL, and voilà!