+91 – 88617 28680learning@flexmind.co

Creating Power BI Report in SharePoint Online

9
Oct

Creating Power BI Report in SharePoint Online

In this blog, you will learn how to create & embed a Power BI report in SharePoint Online using SharePoint List as a data source.

Scenario:

We are going to create a Power BI report that will show

  1. A pie chart showing sales distribution by countries
  2. Bar charts showing Sales based on countries, months product sales for different countries

 

Power BI Report

Power BI Report

We will use 4 different lists to store product sales information.

SharePoint Lists:

  • Product: This list stores metadata ProductKey(number) & ProductName(Text)

Product

  • Sales Territory: This list stores SalesTerritoryKey(Number), SalesTerritoryRegion(Choice), SalesTerritoryCountry(Choice)

Sales Territory

  • Sales Target: This list stores Sales Month(Text) & Target(Number)

Sales Target

  • Product Sales: This list stores product sales information
    ProductKey(LookUp) – look up to Product List ProductKey
    SalesOrderNumber(Text)
    UnitPrice(Number)
    ExtendedAmount(Number)
    SalesTerritoryKey(LookUp) – look up to Sales Territory list SalesTerritoryKey
    SalesMonth(Choice)

Product Sales

 

Once data is properly populated, we can go ahead and start designing our report. you should have proper license assigned to your account before you can design, publish & share a Power BI report. you can find licensing details here.

Download Power BI Desktop here.

  • Launch Power BI Desktop –> Get Data –> More

Get Data

  • Click Online Services -> SharePoint Online List –> Connect

  • Provide SharePoint Online Site URL where you have created Lists.

connect to site

  • Select the lists that we created previously and click on Edit
  • Select Product Sales table from left navigation, change type of column ExtendedAmount & UnitPrice to Decimal Number & select apply.

Change Column Type

  • Select Sales Target table from left navigation, change type of column Target to Decimal Number. Select Close & Apply.

  • Click on Manage Relationships from top navigation. we will delete existing relationships that Power BI has detected based on columns identical name and define our own relationship.
  • Click New –> select relationship for 3 columns productkey, salesterritorykey & sales month as show here in the screenshot.product key relationsales territory key relationsales month relationrelationships
  • Select New Visual from top ribbon menu –> select this newly added visual on the screen and change it to pie-chart from right menu under Visualizations.

 

  • Expand Product Sales table from right navigation, drag and drop ExtendedAmount field to Values. Expand Sales Territory table, drag & drop SalesTerritoryCountry field to Details.

fields

  • Now you should be able to see a pie-chart which shows sales distribution based on countries.

pie-chart

  • Add new visual to include a bar-chart. Expand Product Sales table, drag & drop Extended Amount under Value & Sales Month under Axis as show here:Sales by Month
  • Similarly, you can add other visuals on the same page, drag & drop appropriate fields. In my case, my final report looks like this:

Final Report

 

  • Once report is ready, you can publish it to your Office 365 Power BI workspace from Power BI desktop.

 

Publish Report

 

  • Once published, you can open this report on web browser by navigating to My Workspace in Power BI.

Web Report

 

  • To embed this Power BI report in a SharePoint Online Page, Click on File –> Embed in SharePoint Online. Copy the web link.

Embed Report

  • Navigate to a Modern SharePoint Online Page, insert Power BI webpart & paste the embed link in Power BI report link as shown here:

SharePoint Embed Power BI Report

 

Congratulations! You have successfully created Power BI report & added it to SharePoint Online. Subscribe to our social channel to stay informed about upcoming blogs!

CTA - Power BI

Leave a Reply