cancel
Showing results for 
Search instead for 
Did you mean: 
DeepakS

Realtime Power BI Report from SharePoint Data using Power Automate

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.

image.png

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.

Pre-read

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...)

SOLUTION

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

image-1.png

Power BI Dataset :

  1. Login to Power BI service and create a new streaming dataset
  2. Select API as source

image-4.png

  1. 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.

**Tip:

  1. Do not add all the columns available in the SharePoint list. Use the only column that you will use in the report.
  2. 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.

image-6.png

  1. Click Done

Power Automate

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.

  1. Step 1: Log in to Power Automate and go to the custom connector page.
  2. Step 2: Click on New Connector and select “Import from OpenAPI file.”
  3. Step 3: Give a name to your connector and import the Custom Connector file you downloaded from my GitHub.
  4. Step 4: Go to Security page (** You can update icon or description on the general page do not change other details)
  5. Step 5: To get the unique client id and secrete, we need to register the app.
    1. Go to dev.powerbi.com/apps.
    2. Select Sign in with your existing account, then select Next
    3. Provide an Application Name
    4. Provide Application Type:- “Server-side web application….”
    5. Provide Home Page URLhttps://www.powerbi.com
    6. Redirect URL : https://global.consent.azure-apim.net/redirect
    7. API access: Select All
    8. Click Register and save APP Id and secrete, as we use it in the next step.
  1. Step 5: Go back to Power Automate custom connectors screen and update following
  1. Clint ID = App Id (generated in previous step)
  2. Client secret = App secret (generated in previous step)
  3. Resource URL = https://analysis.windows.net/powerbi/api

image-8.png

  1. Step 6 : Click on Create Connector
  2. Step 7 : Go to the “Test” page and click on “New connection” (if you don’t see any connection there)
  3. 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.

  1. Create a Power Automate with the trigger “When an item is created or modified” (You can use any other trigger as needed)
  2. 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
  3. Add custom connecter from the custom action section and select the “DeleteDataRows” action.
  4. Provide dataset id of your streaming dataset in datasetId field and “RealTimeData” in “tablename” field.
  5. Add a apply to each loop to loop through each item in the “Get Items” response.
    1. Add new action available form Power BI – “Add rows to a dataset.”
    2. Select the streaming data set that we have created and provided mapping for each field
  6. Save the flow and test.

image-9.png

Conclusion

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.

https://deepakshrivastava.com/2020/09/07/realtime-power-bi-reports-with-sharepoint-data-using-power-...

Comments
About the Author
  • Experienced Consultant with a demonstrated history of working in the information technology and services industry. Skilled in Office 365, Azure, SharePoint Online, PowerShell, Nintex, K2, SharePoint Designer workflow automation, PowerApps, Microsoft Flow, PowerShell, Active Directory, Operating Systems, Networking, and JavaScript. Strong consulting professional with a Bachelor of Engineering (B.E.) focused in Information Technology from Mumbai University.
  • I am a Microsoft Business Applications MVP and a Senior Manager at EY. I am a technology enthusiast and problem solver. I work/speak/blog/Vlog on Microsoft technology, including Office 365, Power Apps, Power Automate, SharePoint, and Teams Etc. I am helping global clients on Power Platform adoption and empowering them with Power Platform possibilities, capabilities, and easiness. I am a leader of the Houston Power Platform User Group and Power Automate community superuser. I love traveling , exploring new places, and meeting people from different cultures.
  • Read more about me and my achievements at: https://ganeshsanapblogs.wordpress.com/about MCT | SharePoint, Microsoft 365 and Power Platform Consultant | Contributor on SharePoint StackExchange, MSFT Techcommunity
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • Founder of SKILLFUL SARDINE, a company focused on productivity and the Power Platform. You can find me on LinkedIn: https://linkedin.com/in/manueltgomes and twitter http://twitter.com/manueltgomes. I also write at https://www.manueltgomes.com, so if you want some Power Automate, SharePoint or Power Apps content I'm your guy 🙂
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 15 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • Big fan of Power Platform technologies and implemented many solutions.
  • Passionate #Programmer #SharePoint #SPFx #M365 #Power Platform| Microsoft MVP | SharePoint StackOverflow, Github, PnP contributor
  • Web site – https://kamdaryash.wordpress.com Youtube channel - https://www.youtube.com/channel/UCM149rFkLNgerSvgDVeYTZQ/