How to Create Power bi report from SharePoint Online list

In this Power BI Tutorial, we will discuss how to create a power bi report from SharePoint online list.

[thrive_leads id=’18556′]

I have a SharePoint online list which has few columns like below:

If you are new to power bi a check out an article on what is power bi and install power bi desktop in your local machine. We will use a power bi desktop to create the report where we will add some visualizations also.

• Create a Power BI report from Excel using Power BI Desktop

Create a power bi report from SharePoint online list

Follow the below steps to create a power bi report from SharePoint online list using power bi desktop.

Open power bi desktop. Then, from the Ribbon click on Home tab -> get data, Then from common data sources click on more like below:

This will open get data dialog box which will display the various data sources.

Select online services -> SharePoint online list and click on connect like below:

This will open a dialog box where you can enter the SharePoint site URL and click on Ok like below. If you are creating the report for the first time then it might ask you to enter the credentials which will be having access to the SharePoint online list or the SharePoint site.

In the next dialogue box,  it will display all the lists which are presented in the SharePoint site. Select the lists where your data is presented and you want to create the report.

I have selected the product list and the vendors list like below:

Share you will see two options Load and Transform Data.

If you will select load then it will load this two list data into the power bi desktop, but if you will select transform data then it will open the Power query editor where we can modify the data like we can select the number of columns we require or we can change the data type also.

Here I will select transform data like below:

This will open the power query editor from where we can do some formatting of our data.

Select the required columns for the power bi report

By default, the Power query editor displays all the default columns from the SharePoint online list,  but we might not require all the columns in our power bi report. so here we can select the columns,  which you required for the report. from the left side.

From the ribbon. go to home -> Choose Columns  like below.

In the choose column dialogue box,  it will display all the columns, you can simply uncheck all the columns and check the columns which we require only for our reporting. Here I have selected a few columns like below, Click on Ok.

Now you can see the data source looks like below which can have only the selected columns rather displaying all the list columns.

Format SharePoint list lookup column

If you have any lookup column in the SharePoint list, then it will display like Record instead of the column value.

But we can format that so that it will display the values of each row.

Click on the little icon,  then contact all the columns and just uncheck which column you required like below and click on Ok.

 Now you can see it will display proper values in each record like below:

Change data type of column in power bi

Another important thing we have to do for creating a report in power bi is to change the data type of the columns.

Follow the below steps to change the data type of a column in power bi.

Select the column and then from the home tab click on data type, by default it will be of type any, but you can select the proper data type. This is a date column, so I have selected the data type as date.

Note:  if you are connecting from an excel,  mostly water type will come properly. but in case of  SharePoint, it will display data type any which we have to change before creating a report in power bi.

In the same way, you can change the data type for the other columns, for the numeric data type you can select, whole number, for a single line of text you can select the data type as text.  and for currency type, you can select the data type as currency.

You can also select multiple columns by pressing the control key and you can change the read attack at once. For example, here I can select all the columns which I want to make numeric and then change the data type to the whole number.

Rename Column Names in Power BI

We can also rename the column names as for our requirements. To rename a column in power bi, right-click on the column and click on rename and then you can change the column name.

Or you can also go to the transform tab and then click on the rename button like below. We can also rename a power bi a column from the properties.

This finishes our data formatting and now we can create our report in power bi.

From the home tab click on close and apply button below:

Here, I have also renamed the default Title column to Product Name.

Add Visualization into Power BI Report

We can add various visualizations from the report tab in power bi

Here I have selected donut chart from the list of visualizations like below:

Once we added the donut chart, we can set of the properties so that the chart will appear.

In the legend, I have added the product name.

And in the Values I have added the Quantity like below:

Now, the Power BI chart will appear like below:

Click on the Save button to Save the Power BI report.

You may like following Power BI tutorials:

• Power bi free vs pro vs premium
• What is the difference between calculated column and measure in Power BI
• How to create and use Power BI Bookmarks

Conclusion

This tutorial, we learned the below things:

• How to create power bi report from sharepoint online list
• Select the required columns for the power bi report
• How to Format SharePoint list lookup column
• How to Change the data type of column in power bi
• How to Rename Column Names in Power BI
• How to Add Visualization into Power BI Report