Are you new to Power BI? I’ll guide you step by step on how to create a report from SharePoint list in Power BI. We’ll start from the beginning and walk through the process together. In this article, I will use the Table, column chart, Bar chart, Donut chart, Chart, and Line chart in the Power BI report.
To create a Power BI report from a SharePoint list, first, connect Power BI to your SharePoint list by selecting “Get Data” and choosing “SharePoint Online List” as the source. Next, import the desired list into Power BI, and then use Power BI’s visuals to design and customize your report. You can use the Table, column chart, Bar chart, Donut chart, Chart, and Line chart in the Power BI report.
Here, we will use a SharePoint List to create the report in Power BI. So, ensure you have a SharePoint List, like the one below. This SharePoint online list has a few columns, as shown in the screenshot below.
We need to connect to the SharePoint site to create a report from a SharePoint list in Power BI.
Connect to your SharePoint List From Power BI
1. Open the Power BI desktop. Then, from the top navigation bar, click on Get data -> More…
2. The Get Data dialog box will open; click on Online Services -> SharePoint Online List, then Connect.
3. This SharePoint Online Lists will open a dialog box where you can enter the SharePoint site URL. Click ‘OK’ to proceed. If you creating a report for the first time, it will prompt you to enter your username and password access to the SharePoint online list or site.
4. After that, it will display all the lists and libraries on the SharePoint site. Select the list you want to use in this Power BI report. If your list has clean data, click the Load option. Otherwise, click Transform Data.
5. In my case, I click Transform Data. Then, the power query editor opens, and you can see it. I am removing the columns; in the Top navigation, click Home and click the Choose Columns option in the ribbon.
6. When you click, the Choose Columns dialog box opens. By default, Power BI will load all the list columns. You can choose the columns you need to create a report. Then click ‘OK’.
7. Click on the “Home” option in the top navigation, then click Close & Apply on the top left side. Then, in the right-side Data navigation, you will see the table and the corresponding columns that we can use in the Power BI report.
Now, we can create our report by using the above data. For this, you can use any visuals presented in the Visualizations tab. I will add a few visuals and show you how to configure the visuals to display data from the SharePoint list.
Add Table Visual to Power BI report
I will now guide you through creating a table visual that displays only the Country names. In the Visualizations navigation, select the ‘Table’ visual option. Then, drag ‘Country’ from the Data navigation and drop it into the ‘Columns’ field in the Visualizations navigation.
Add a Column chart to the Power BI report
Next, I will show you how to use a Power BI column chat to show the Sum of PRICE by Product. The x-axis should contain the ‘Product’ names, and the y-axis should represent the ‘Sum of Price’ data. This is how the chart will look like:
Add a Bar chart to the Power BI report
The Bar Chart displays the Sum of MRP by Product. You drag ‘Product’ into the ‘Y-axis’ and ‘MRP’ into the ‘X-axis’.
Add a Donut chart to the Power BI report
Now, I will show you a Donut chart that displays the Sum of TOTAL by Product. Click the ‘Donut’ in the Visualizations navigation. Then drag ‘Product’ into the ‘Legend’ field and ‘TOTAL’ into the ‘Values’ field. You can see the screenshot below of how the Donut chart looks like in the Power BI report.
Add a Card to the Power BI report
In this report, you see a Card visual that shows the Sum of AMOUNT in a display. Click the ‘Card’ option in the Visualizations navigation. Then, drag the ‘Amount’ column in the Data navigation and drop it into the Visualizations navigation Data field.
Add a Line chart to the Power BI report
I will create a Line chart that displays the Sum of Price and Sum of MRP by Product in which the X-axis field contains the ‘Product’ column, and the Y-axis field contains the ‘Sum of MRP and Sum of PRICE‘ data.
After creating our report successfully, the final Power BI report from the SharePoint list data looks like the screenshot below.
You can save it by clicking on the File menu in the top-left corner, then selecting Save or Save As Option. Then, choose a location and provide a name for your report file. This will ensure that your work is preserved and can be accessed later.
Conclusion
In this Power BI tutorial, I have explained how to connect to a SharePoint site from Power BI. Then, step by step, I explained how to create a report in Power BI from SharePoint list data. Finally, we learned how to use various visuals like table, column chart, bar chart, donut chart, card, and line chart.
You may also like:
Preeti Sahu is an expert in Power Apps and has more than 6 years of experience working with SharePoint and the Power Platform. As a Power Platform expert for Power BI, Power Apps, Power Automate, Power Virtual Agents, and Power Pages, she is currently employed with TSinfo Technologies. She is the author of the book Microsoft Power Platform A Deep Dive. She also made a big technical contribution to SharePointDotNet.com in the form of articles on the Power Platform. She enjoys traveling and spending time with her family in her spare time.