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 is a Power BI report?
• Key features of a Microsoft Power BI Report
• How to create a power bi report from a sharepoint list
• How to create a Power BI Report from Excel data
• Customize Power BI visualizations
• How to change the theme of the 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 consisting of 8 different pages or dashboards. And each dashboard has one or more visuals.

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 the 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 creating 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:

Step-2:

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

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.

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.

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.

Step-6:

The updated data will be look this:

*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.

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]

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:

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:

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:

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

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:

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.

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.

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.

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.

Step-2:

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

Step-3:

Then it will show a success message like below:

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.

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.

Step-7:

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

Step-8:

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

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.

Then Copy the link. Click on Close.

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 in the power bi report on the SharePoint site.

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 mobile devices.

Go to File > Generate QR code.

Then it generates the QR code like below:

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.

Step-3:

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

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.

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.

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:

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.

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:

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

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

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:

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:

• 5 Simple and Powerful Power BI dashboard examples
• How to combine multiple files from SharePoint folder in Power BI
• Display COVID-19 Power bi report in PowerApps
• How to embed Power bi report in PowerApps portal
• Power BI: How to Connect Most Recent File from a Folder
• How to use Power BI Desktop

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