How to Create Power BI Report from SharePoint list and Excel?

While working with Power BI Desktop, I learned I could connect to many data sources with Power BI, especially SharePoint lists and Excel. Using these data sources, we can create Power BI reports.

So, I thought I would discuss with you what a Power BI report is and how to create one using data from a SharePoint list and Excel.

What is Microsoft Power BI Report?

Microsoft Power BI is a tool for interactive data visualization and business analysis.

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.

For example, the screenshot below is a Power BI report showing various charts.

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

Connect SharePoint List in Power BI

First, I will show you how to connect to the SharePoint list in Power BI. So, ensure you have a SharePoint List. For example, here I have created a SharePoint list (Sales Data) and added a few data:

1. Open Power BI Desktop, under the Home tab, expand the Get data option, then click More…

2. The Get Data dialog box will open. Click on Online Services, choose SharePoint Online List, and click Connect.

3. After that, a dialog box will open where you can enter the SharePoint site URL. Choose Implementation 2.0, expand Advanced options, and then select View Mode. I selected Default – Retrieve the column set in the “Default view” of a SharePoint List. You can choose according to your requirements, then click ‘OK’ to proceed.

If this is your first time connecting to the SharePoint list in Power BI, you may be prompted to enter your username and password to access the SharePoint online list or site.

4. A Navigator page will open. It will display all the lists and libraries on the SharePoint site. Select the lists you want to use in this Power BI report. If your list has clean data, click the Load option. Otherwise, click Transform Data.

I click Transform Data to remove the last row, which is the null value, as shown in the screenshot below.

Deleting the Last Row Using Power Query Editor

1. Then, it will open a Power Query Editor. Under the Home tab, expand Removes Rowers, then select Remove Bottom Rows.

2. Then you can see a dialog box will open where you can enter the number of rows you want to remove. Then click OK.

3. Then you can see the row will removed before it was 20 rows. Now it is 19 rows.

4. Our data is cleaned, so under the File tab, click Apply & close.

5. Then, you can see data in the Data pane.

This way, you can connect to your SharePoint list in Power BI.

Connect to Excel in Power BI

Now, we will see how to receive data from Excel using Power BI Desktop.

I have an Excel file named “Order Details” that contains a table called “AmazonGo.”

1. Open Power BI Desktop under the Home tab, expand the Get data option, then click Excel workbook.

2. Then select your Excel file and click Open.

3. A navigator page will be open. Now we can see all the tables and sheets. Then, choose which you want here. If your tables and sheets have clean data, click the Load option. Otherwise, click Transform Data.

Here, I click Load Option.

4. Then, you can see data in the Data pane.

This way, you can connect to your Excel in Power BI.

Create a report in Power BI

Now, we can create our report by using the data in the Data pane, and you can use any visuals presented in the Build tab.

Add a Pie Char to the Power BI Report

To display a pie chart visualizing the Sales by Customer Name, follow these steps:

1. In the Build pane, click on “Pie chart.”
2. Drag and drop “Customer Name” to the legend field.
3. Drag and drop “Sales” to the Values field.

The pie chart will then look like this:

Add a Column Chart to the Power BI Report

To display a Column chart visualizing the Profit by Product Name, follow these steps:

1. In the Build pane, click on “Stacked column chart.”
2. Drag and drop “Product Name” to the X-axis.
3. Drag and drop “Profit” to the Y-axis.

Then, the column chart will look like this:

Add a Slicer Visual to the Power BI Report

To display slicer visuals for slicing by Region:

1. In the Build pane, click on the “slicer” visual.
2. Drag and drop “Region” to the Fields.

Add a Card Visual to the Power BI Report

Now, I want to add a card visual that displays the quantity. To do this, in the Build pane, click Card Visual, then drag and drop Quentity in the Fields.

Add a Waterfall Chart to the Power BI Report

To display a Waterfall chart visualizing the Sales by City, follow these steps:

1. In the Build pane, click on “Waterfall chart.”
2. Drag and drop “City” to the Category field.
3. Drag and drop “Sales” to the Y-axis.

After creating our report successfully, the final Power BI report looks like the screenshot below.

Saving the Power BI Report

You can save it by clicking on the File menu in the top-left corner, then selecting Save or Save As Option.

Then, you provide the name of your report and click save.

Now, I hope you can connect the SharePoint list and Excel file to the Power BI Desktop. Also, you know how to create a Powet BI report using various visualizations.

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.