How to create Power BI report from SharePoint list + Excel

In this Power BI Tutorial, we will discuss what is a report in Power BI, step by step, how to create power bi report from sharepoint list. We will also discuss how to create a report in power bi using excel.

  • What a Power BI report?
  • Key features of a Microsoft Power BI Report
  • How to create power bi report from a sharepoint list
  • How to create a Power BI Report from Excel data
  • Customize Power BI visualizations
  • How to change theme of Power bi report
  • How to save, publish and share a Power BI Report
  • How to get embedded report URL in SharePoint Online
  • How to create a QR code for Power BI Report

What is Report in Microsoft Power BI?

Microsoft Power BI is an interactive data visualization and business analytical tool.

Microsoft Power BI Report is a collection of pages and dashboards in a single file. Reports are created by a Power BI desktop designer. It allows many different ways to filter, highlight, and slice. Here is an example of a power bi report consists of 8 different pages or dashboards. And each dashboard has one or more visuals.

What is Report in Microsoft Power BI
Example of Power BI Report

Read: Power bi best practices

Key features of a Microsoft Power BI Report

Here we will discuss what are the key features of a Power BI Report. Some important features are:

  • Slicers: It represents the visuals and lets the user select values for analyzing a Report.
  • Multi-pages: A Power BI Report consists of a number of Dashboards or pages that interact with each other.
  • Filtering data: In Power BI Reports it allows to filter the data based on some criteria.
  • Publish to web: The user can publish their Report to the web for future reference.
  • Customization: By customization, the user can make changes to the visualization of a Report.
  • Interactive: If you select a particular element, then it will highlight other elements. It means all the data of a Report are interactive with each other.

Check out: Microsoft Power bi report vs dashboard

How to create Power bi report from SharePoint list?

If you have not installed Power BI desktop, download and install Power BI desktop first.

Now we will see how to create a Power BI Report using SharePoint list. Here is the step by step guide to create a power bi report from sharepoint list:

Step-1:

For creating a Power BI Report from the SharePoint list, first, we have to prepare a SharePoint list. For example, here I have created a Products list and added few data:

How to create a Power BI report from SharePoint list
How to create a Power BI report from SharePoint list

Step-2:

Open Power bi desktop > Get data > more… > Online Services >SharePoint Online list > Connect.

How to get data from SharePoint Online on Power BI?
How to get data from SharePoint Online on Power BI?

Step-3:

After connecting it will redirect a dialogue page which will ask site URL of the SharePoint list. Enter here the site URL of your corresponding SharePoint list. Click ok.

connect SharePoint list to Power BI by Site URL
connect SharePoint list to Power BI by Site URL

Step-4:

A navigator page will be open. Now you have to navigate or select the lists, by which we will create a report. Then it will preview the data on the navigator page.

How to Navigate data from navigator to Power BI
How to Navigate data from navigator to Power BI

Step-5:

Click on Transform Data. It will open a Power query editor, remove all the unnecessary columns except those columns which are from the SharePoint list.

We can see on vendors no data is there. For this, you have to click on the arrow symbol at the top corner of the column. Then unchecked everything and selects the only Title > OK.

how to create report in power bi
Power BI Query editor

Step-6:

The updated data will be look this:

how to create power bi report from sharepoint list
how to create power bi report from sharepoint list

*You can also rename the column name by double clicking on a particular column name and click enter key to save.

Now we will see how to format the data in power query editor:

  • Select a column, that you want to format.
  • In home/transform tab > data type > click on drop-down.
  • Format your data according to your need. For example, we change the delivery charges, quantity, price column’s data type any to whole number.
create power bi report from sharepoint list
How to do Format data types on a Query Editor

Also, we formatted the Sells Date column to date format and products name, vendors name, city to text format.

To display the Total Price on the Power BI Report, we need to create a column to the data table.

Here click on the New column and add the below formula in the Formula bar like below, Once you save the formula, you can see the Total Price columns have been added and the values have been populated

Total Price = Products[Quantity]*Products[Price]
how to create report in power bi desktop
How to create new column on Power BI

Now the Data is ready to create a report in power bi from the SharePoint list. Let’s have a look at the following steps:

Step-7:

Now, we need to create a report. For this, click on the Report icon (left side) which will open the Report page

We select a pie chart for creating visuals that visualize the total price by product name. For this visualization, you have to select the product name and total price from Fields. Then the visualization will look like this:

 Create Pie Chart on Power BI
Create Pie Chart on Power BI

Now we will add a slicer that will slice and reflect the data on the pie chart. For this, select slicer from visualizations and add customer name from the field. Now the page will appear like this:

how to create report in power bi desktop
create a Slicer on Power BI

Here it’s slice or filter the visualization by according the customer name(John).

At the bottom of the page, you can add more pages on click that add + symbol. Rename it.

As we described that a report consists of multi-pages dashboards and a dashboard is a single page visualization. So in this example, we have to add more pages to create visualizations.

Now we will add another page to this report, which consists of more than one visuals and that visualizes the data in various ways. First, we add a waterfall chart that shows the price by customer name. Here is the visualization:

How to create a Waterfall chart on Power bi
How to create Waterfall chart on Power bi

Similarly, we will add another visual of the treemap, on that same page that will show the quantity by customer name.

create treemap on Power BI
create treemap on Power BI

Now we will add another page that shows the total price, total quantity, total delivery charges. Also, the data visualized in a multi-row card that contains the total price, quantity, delivery charges, city. Now the page will look like this:

 create a Multi-row card and Card example on Power BI
create a Multi-row card and Card example on Power BI

This is how to create a Power BI Report on Microsoft Power BI.

Read: How to Create a Dashboard in Power Bi?

Customize Power BI visualizations

There is various type of functions in power bi visualizations, by which you can customize the Power BI reports. There is an option Format. Click on that. You can see the options like:

  • General: In this section, you can edit the position of visuals according to X-axis, Y-axis, height, width, etc.
  • Data label: It is used to edit the color, font size, font family.
  • Category: Here you can customize the category color, font, size.
  • Card Title: You can add the title, its color, the font size by expanding this card title.
  • Title: Here you can add the title and font color to a particular visualization.
  • Background: You can add background color to visuals. So on.
Customize Power BI visualizations
Customize Power BI visualizations

This is how to create and customize a Power BI report.

How to change theme of Power bi report

We can change the light format or theme on the power bi report. On view tab, select themes > Select drop down

There you can find more option , according to your requirement you can change the theme.

How to change themes on Microsoft Power BI
How to change themes on Microsoft Power BI

This is how to change a theme in Power BI report.

Now, let us see, how to save, publish and share a Power BI report.

How to save, publish and share a Power BI Report?

Now we will see how to save a Power BI Report to the desktop.

Step-1:

Click on File >Save as.

Step-2:

Select Folder > Give a Name to Your Report > Save.

We can see the name on the top of the Power BI Report.

How to save Power BI Report on desktop
How to save Power BI Report on desktop

Now we will see how to share and publish the report:

Before publish and share, we will have to follow some rules. These are:

  • You can share the power bi reports with the people inside or outside of the organizations.
  • You can not share the report directly from the desktop. For this you require Power bi pro license.
  • When you share the report with people, they only view or interact with it but can not able to edit.
  • Power bi send the email invitation individually but not in groups.
  • When you share a report with your co-worker, they can also share with their co-worker if you permit. Let’s see how to publish the power bi report by following this steps:

Step-1:

From Power BI Desktop, click on File > Publish > Publish to Power Bi.

 Publish Report on Power BI Service
Publish Report on Power BI Service

Step-2:

Then select a destination to publish. i.e. My workspace>Select.

Select a destination to publish  Report
Select a destination to publish Report

Step-3:

Then it will show a success message like below:

Publish Report On Power Bi Service
Publish Report On Power Bi Service

Step-4:

Once successfully published, click on the above link “Open ‘Power Bi report.pbix’ in Power BI”, or you can directly visit Power BI Service > My workspace > Reports > Power bi report.

 get Power BI report on Power BI Service
get Power BI report on Power BI Service

Step-5:

To share with other users, click on the Share.

Step-6:

By clicking on the share option, a dialogue page will appear having several options. You can copy the link and share it with other users. Also, you can share reports on outlook and team.

Share option Power BI report
Share option Power BI report

Step-7:

Here we share this report to another user of our organization.

share report through a link on power BI Service
share report through a link on power BI Service

Step-8:

Once you click on Send, You can see a success message like this:

Power BI Report Successfully sent notification
Power BI Report Successfully sent notification

Step-9:

Then the user will receive an email notification. Users can click on the Open this report link and can see the report.

How to get embedded report URL in SharePoint Online

We can get an embedded report URL, which can be added to SharePoint Online. For this, Click on File > Embed report > SharePoint Online.

embed report on SharePoint Online
embed report on SharePoint Online

Then Copy the link. Click on Close.

How to get Embed link for SharePoint
How to get Embed link for SharePoint

Go to your SharePoint site. Create a blank webpage.

Add a Power bi Web Part > Add a report> Paste the Embed link on the field.

It will appear the power bi report on SharePoint site.

How to Embed Power BI Report on SharePoint Online
How to Embed Power BI Report on SharePoint Online

Click on Publish. You can select the Page name and display ratio from the drop down.

Read: How to Embed Power BI Report in SharePoint Online

How to create a QR code for Power BI Report

We can create a QR code for a particular Power Bi report. Users can scan or download this QR code to see the report directly on a mobile devices.

Go to File > Generate QR code.

Then it generating the QR code like below:

create a QR code for Power BI Report
Generate QR code for power bi report on Power BI

How to Create a Power BI Report from Excel data

Now we will see how to create a Power BI Report from Excel. Here is the step by step guide to create Power BI Report from Excel sheet.

Step-1:

For creating a Power BI Report from Excel, first, we have to prepare an excel data. For example, here we use an excel data, which downloaded it from browser. You can Download it from here.

Step-2:

Open Power BI Desktop > Get Data > More…>All/File > Excel > Connect.

How to get data from excel on Power BI?
How to get data from excel on Power BI?

Step-3:

After connecting, it will redirect to the folder location. Select a Data > Click Open.

Connect Excel data to Power BI Desktop
Connect Excel data to Power BI Desktop

Step-4:

A navigator page will be open. Now we have to navigate the excel data, by which we will create a report. Then it will preview the data on the navigator page.

How to navigate Excel data from navigator to Power BI
How to navigate Excel data from navigator to Power BI

Step-5:

Click on Load. Now the data will be load on Power BI Desktop. We can check it by clicking on the Data segment.

How to check Loaded Data on Power BI
How to check Loaded Data on Power BI

Now the data is ready to create report on Power BI.

Now, we need to create a report. For this, click on the Report icon (left side) which will open the Report page.

Step-6:

To create our first visual, take a Pie chart from visualizations, which visualize Profit by Product. For this visual we take:

  • Legend: Products
  • Values: Profit

Then the visualizations will look like below:

How to Create a Pie Chart on Power BI
How to Create a Pie Chart on Power BI

On this same page, let’s add a slicer from visualization, that slice or filter the Profit by Product data on Pie chart according to Country.

How to slice data on Power BI Report
How to slice data on Power BI Report

Similarly, let’s create another visual using Stacked Column Chart, which presents the data Profit by Month Name. For this visual, we will take:

  • Axis and Legend: Month name
  • Values: Profit

Then the first report page will be look this:

How to create stacked bar chart on Power BI
How to create stacked bar chart on Power BI

At the bottom of the page, you can add more pages on click that add + symbol. Rename it.

In this example, For creating report we have to add one more page to create visualizations.

Let’s add another page to this report. Here, we will add a Waterfall chart, that presents the data Sale Price by Product. For this chart, we will take these fields:

  • Category: Product
  • Values: Sale Price
How to create a waterfall chart on Power BI
How to create a waterfall chart on Power BI

Similarly, we will add another visual of the line chart, on that same page that will show the profit by Product. For this visual we will take:

  • Axis: Product
  • Values: Profit
How to create a line chart on Power BI
How to create a line chart on Power BI

Let’s add another visual of Clustered Column Chart, that visualizes Manufacturing price by product. Here we will take:

  • Axis: Product
  • Values: Manufacturing price

Then the page will be look like this:

How to create clustered column chart on Power BI
How to create clustered column chart on Power BI

Here, we added only 2 pages to create a report. You can add more Pages on a Power BI report.

This is how to create a Power BI Report using Excel data.

You may like the following Power BI tutorials:

Conclusion

In this tutorial, we learned how to create power bi report from sharepoint list, step by step we saw, how to create a report using Power BI Desktop.

  • What a Power BI report?
  • Key features of a Microsoft Power BI Report
  • How to create power bi report from a sharepoint list
  • How to create a Power BI Report from Excel data
  • Customize Power BI visualizations
  • How to change theme of Power bi report
  • How to save, publish and share a Power BI Report
  • How to get embedded report URL in SharePoint Online
  • How to create a QR code for Power BI Report
>