Often we have a requirement to create a real-time power report for SharePoint data. but, when we connect to SharePoint list data from Power BI, the only option we have is to use import query mode instead of the direct query. This method only allows the data refresh up 8 times per day with Power BI Pro and up to 48times with Power BI Premium.
Power Automat announced new actions “Refresh a dataset,” to allows to refresh the data set from Power Automate, but this action also limited to available refresh under each license category.
So the question is, is there any way we can create a real-time report with SharePoint data in Power BI? , and the answer is yes, this article will explain how.
Before we start the solution, we need to understand the following concept of Power BI and Power Automate.
Types of real-time datasets in Power BI (Read More…)
- Push dataset
- Streaming dataset (We will use this dataset in Hybrid mode)
- PubNub streaming dataset
Power BI Rest API (Read More..)
We will use Push Datasets – Datasets DeleteRows API
Power Automate Custom Connector (Read More...)
This article does a walkthrough on the process to build a real-time report in power bi that is connected to a course registration SharePoint list.
Source Data: Sample Course Registration list contains information like User Name, Status, Registration date, and Course name
Power BI Dataset :
- Login to Power BI service and create a new streaming dataset
2. Select API as source
3. On the next screen, you need to define the various fields that you would like to use in your Power BI report. This step is to define your final dataset that SharePoint will send to Power BI.
Note : Make sure you checked “Historic data analysis” to create a hybrid dataset.
- Do not add all the columns available in the SharePoint list. Use the only column that you will use in the report.
- Try to create fields for summarizing data. You can use the Power Automate/SharePoint to summarize the data and share final results with Power BI.
4. Click Done
In the above steps, we created an SP list with data and Power BI streaming dataset. Now we need a process that will trigger based on a pre-defined condition and add new data to Power BI streaming dataset.
There is no available action to update/delete existing data rows from the streaming dataset. So we will create a custom connector to delete the rows using Power BI Rest API.
You can download the ready to use Custom Connector from here and follow the following steps to import.
- Step 1: Log in to Power Automate and go to the custom connector page.
- Step 2: Click on New Connector and select “Import from OpenAPI file.”
- Step 3: Give a name to your connector and import the Custom Connector file you downloaded from my GitHub.
- Step 4: Go to Security page (** You can update icon or description on the general page do not change other details)
- Step 5: To get the unique client id and secrete, we need to register the app.
- Go to dev.powerbi.com/apps.
- Select Sign in with your existing account, then select Next
- Provide an Application Name
- Provide Application Type:- “Server-side web application….”
- Provide Home Page URL: https://www.powerbi.com
- Redirect URL : https://global.consent.azure-apim.net/redirect
- API access: Select All
- Click Register and save APP Id and secrete, as we use it in the next step.
5. Step 5: Go back to Power Automate custom connectors screen and update following
- Clint ID = App Id (generated in previous step)
- Client secret = App secret (generated in previous step)
- Resource URL = https://analysis.windows.net/powerbi/api
6. Step 6 : Click on Create Connector
7. Step 7 : Go to the “Test” page and click on “New connection” (if you don’t see any connection there)
8. Step 8 : Update the connector and test available operations
Sync Power Automate
The last step is to create a Power Automate that will use the custom connector to push the data to Power BI dataset as SharePoint list item change.
- Create a Power Automate with the trigger “When an item is created or modified” (You can use any other trigger as needed)
- Add an action to Get Items from SharePoint list in this example – “Course Registration List.” You should use an appropriate filter to query only required data
- Add custom connecter from the custom action section and select the “DeleteDataRows” action.
- Provide dataset id of your streaming dataset in datasetId field and “RealTimeData” in “tablename” field.
- Add a apply to each loop to loop through each item in the “Get Items” response.
- Add new action available form Power BI – “Add rows to a dataset.”
- Select the streaming data set that we have created and provided mapping for each field
- Save the flow and test.
Now create a Power BI report using the streaming dataset(created earlier) and add it SharePoint page or any other page as needed. Whenever the user adds/updates data in the SharePoint list, Power Automate will trigger and update the dataset. now your power bi report will show real-time data report connected to SharePoint list.
Check Power Automate and Power BI licensing, as you will require a premium license to create a custom connector