As industry-leading SharePoint consultants, we provide strategy, create and implement solutions, and offer ongoing support across the SharePoint platform.
As with all companies who use SharePoint, the platform becomes a data repository containing many types of valuable business information. So, with all this information residing inside of SharePoint, what can end-users utilize to create reports to help them make better business decision from the data collected? The answer is Power BI.
What is the Power BI online service?
Power BI is an online reporting service provided by Microsoft that gives the subscriber the ability to create reports and dashboards for a single or multiple data sources. Examples of data sources that Power BI can connect to are listed below:
You can leverage the Power BI online service with your on-premises data repository. To do this you must install software called Power BI On-premises data gateway, which gives you the capability to implement hybrid reporting from the cloud. Although the data gateway will not be covered in this tip, you can learn more about it.
In this tip, we will cover how to create reports from data in a SharePoint Office 365 (“O365” or “SharePoint Online”) list and an Excel worksheet. To get started, make sure you have taken care of the following requirements:
Requirements for this configuration to work
- SharePoint Plan license plan 2 or Office 365 license E3 or higher plan
- Microsoft Power BI Pro online service account
- Microsoft Power BI Desktop Application (optional)
What is the Power BI desktop application?
Power BI Desktop is a report authoring tool that lets you create reports from multiple data sources or online services that can be published to the Power BI service.
Create a report from a SharePoint Online list using the Power BI desktop application
Launch the Power BI desktop application. Click on Get Data in the ribbon and select More from the drop down menu.
In the left-hand navigation of the pop-up window, select Online Services, select SharePoint Online List, and then Connect.
In the URL field enter the SharePoint site URL where the list is located, and then select Ok. Enter your credentials if prompted. Once connected, in the Navigator window select the list or lists you would like to connect to.
Select the Edit button to customize the imported data or Load to import the selected data. Once the data is imported we can start creating a report. There are several pre-built visualization templates for use or you can import your own visualization template.
To illustrate, I will create two visualization reports. The first report will be a bar chart and the second will be a pie chart. I will start by creating a bar chart report that will show the profit of the product category by region. This report has the ability to drill-down to the product name to show the profit of that product.
Region and Product Category report:
Drill down to Product Name and Product Category report:
Bar Chat visualization configuration:
The second report will display the same information but will display in a pie chart with the ability to drill-down one level in the data.
Profit by Region report:
Drill-down to Profit by Region and Product Category:
Pie Chart visualization configuration:
Next let’s create another data set called Sales Users and append it to the Store Sales data set. First select the bar chart report in the ribbon and then select Edit Queries. The Query editor window appears. Within the Query editor ribbon, select the Combine icon.
Then select Append Queries and Ok. In the Append window select the Two tables option and then Sales Users in the Table to append dialog. Select Ok.
In the Query Editor window select Close & Apply to append the Sales Users data set to the Store Sales dataset.
Publish a report to the Power BI service from the Power BI desktop application
Now that I have created the Product Profit report, it is time to publish it to Power BI. In the Power BI desktop application ribbon, select the Publish icon.
If prompted, enter your Power BI credentials. Once published, you can now share the report with other users that have a Power BI account.
Product Category and Region Profit report:
Creating content packs and dashboards
With the report loaded in Power BI, I can now create what is called a content pack. A content pack saves a report, dataset, or dashboard as a template for my organization or for a specific user. The “View content pack” option displays which content packs have been published. The “Manage gateways” option shows connections to your on-premises gateway server.
Power BI allows you to create dashboards. Let’s pin the Product Profit report to a newly created dashboard named “Product Profit dash.”
To create a Dashboard, I will select the Product Profit report and click on the thumbtack icon.
The Pin to Dashboard pop-up window opens, and here I’ll select New dashboard. Let’s call the dashboard “Product Profit dash” and select Pin.
Using Natural Query Language to filter data
With the report saved as a dashboard, users can leverage the Natural Query Language to filter the data. For example, I can use the Product Profit report from the dashboard to filter by product name with the lowest profit.
To access my report within the dashboard, I select Product Profit and type “product names lowest profit” in the Ask a question about your data field.
Product Category and Region Profit dashboard:
What other options are available with Power BI?
Displaying a Power BI report in SharePoint Online
In order to display in SharePoint Online a report that you have published to Power BI, you will need to meet the following requirements:
- Your Office 365 instance will need to be setup with the first release option.
- The first release option must be set for everyone.
- SharePoint Online must use Modern Pages.
- Any user that will view the embedded report will need a Power BI Pro subscription.
For more information about integrating Power BI reports with SharePoint Online, click here.
Scheduling a data refresh
You can schedule your report to sync to its dataset so the data remains up to date. Let’s setup a schedule to refresh the data for our report. In the Power BI service ribbon select the gear icon.
Select Settings from the dropdown menu. In the Settings menu select the Datasets tab. In the data source credentials section provide the login information for an account that has owner permission to the data source. Under the Scheduled refresh section turn on the Keep your data up to date slider. There are only two types of refresh – Daily or Weekly. Select Daily for this example. Schedule the refresh for 11:30 AM Eastern and select Apply.
You can also export reports into PowerPoint so you can insert your report into a presentation.
This is just a sample of what you can do with the Power BI reporting service. By utilizing Power BI, reports can be created and deployed by any user with a subscription, and the user doesn’t have to be a developer or a report writer to do it. This is a powerful tool to create meaningful, visual reports using the existing information that you already have in SharePoint or external data.
As a Microsoft Gold Partner, we offer Microsoft 365-based solutions to help businesses glean vital insights via business intelligence.
If you’d like to learn more about how our SharePoint solutions can transform your business, visit our SharePoint Consulting page.
If you would like some more information on topics we covered, please visit these sites:
This tip written by Abel Solutions Senior SharePoint Consultant Marlon Roberts.