Scenario
To create Power BI report from a SharePoint list, we may need Power BI desktop tool. Once we have installed the Power BI Desktop tool, we need a Power BI expert who can create some visuals from the list structure and its available data. But what if we come to know that we can create basic data visualization of SharePoint list in Power BI without having any Power BI expert with us? Let's explore this into this article.
Introduction
Microsoft released new feature “SharePoint: Automatically build visualization of list data with Power BI” in August 2022. The intention of this feature is to enable SharePoint list users to easily visualize their list data in a Power BI report. The initial report is auto-generated. Users can also save that report so that future visitors to the list can view the report.
Consider a list with given structure.
And now, we need to generate some basic Power BI report using this SharePoint list. So let's see how we can generate that Power BI visualization in few clicks.
Generating Power BI Report from SharePoint Online List
Step 1
Open the SharePoint list from which you want to build the Power BI report.
Step 2
Click on Integrate option on the list menu and then click on Power BI
And then click on "Visualize the list" option.
Then you will be redirected to Power BI web service dashboard page. And there you have to login with the user account which have Power BI access.
After login, go back to SharePoint list and again click on "Integrate -> Power BI -> Visualize the list". and then you will see a new page where your report building is in progress.
Once the report is generated, you will see some of the basic and simple visuals are already on the Power BI page and you can now play with it.
Save/Publish the report to SharePoint list
To publish the report back to SharePoint list, we can click on the link “Publish to the list”
Enter the name for the report and click on Publish button
It will publish the report to the SharePoint list and display below message in top right corner of the page. It means now your report is published to the SharePoint list
How to view published reports on SharePoint list?
To view the reports published on the list, open the SharePoint list view page, then click on “Integrate” option and then click on “Power BI” and then you will see your available report names.
Click on the report name and it will open your report in Power BI web service.
Editing the report
Whenever we create a Power BI report on SharePoint list using “Integrate” option, it will generate some basic visuals based on its AI capabilities. But it is not always required to use that report. We can always customize the generated report and make it to satisfy business needs.
To edit the report, re-open the report and click on “Edit” icon/link.
It will ask you to switch in edit mode. Click Continue button on this.
And now report will be opened in Edit mode and now we can update the Power BI report and add edit the report to achieve business needs
Once the editing is done, again click on “Publish to the list” link from top right corner and report will be published again.
Synchronizing with List Structure changes
Consider once you have published the report and after publishing the report, someone has added or deleted any column from the SharePoint list. Then to accommodate those list structure changes into the report, we can synchronize the Power BI report with latest SharePoint list structure in one click.
To do this, open the report associated with the list in which you want to synchronize the changes, then click on “File” menu and then click on “Sync list structure” link
Once you click on this link it will start synchronizing the list structure with the Power BI report and made necessary changes wherever it is required. So next time when report will be edited, it will have those new columns added into the right-side panel or it will remove deleted columns from the outside right panel.
Deleting the report from SharePoint list
To delete the existing report of SharePoint list, open the report and click on “Delete” link from the menu bar of the report
Once you click on “Delete” button, it will display a warning message that the report will be removed from the list.
Click on “Delete” to delete and “Cancel” to cancel the operation.
Summary
In this article, we have seen how we can create/generate default Power BI report using SharePoint list out of the box features.
If you have any questions/feedback related to this article, please mention that in comment section.