Event-based Power BI dataset refresh with email notification

Last year I was working on a requirement, to implement event-based dataset refresh that can send out an email when the refresh has completed successfully or failed. I came across Power Automate as the tool of choice to achieve this task easily and efficiently. Power Automate becoming main stream into Power BI visual does speak volume for the product’s features.

In this blog, we will implement a Power BI dataset refresh that is triggered when an event like add/ update records takes place. Usually we will want to implement such trigger when a nightly load has completed. We will need three components in place to implement end-to-end solution.

1. SQL Server table – This is like an audit table keeps track of when the load is completed.
2. Custom Connector – This connector will be used to get dataset refresh status using Power BI REST API. This is reusable.
3. Power Automate Flow – This will use above two components, perform the refresh action and send email notification.

Let’s start with the steps

Create SQL table

As mentioned above this table can be an audit/ status table that records when all the rows are loaded/ updated in the main tables. When the loading has completed successfully this table can update the status or load date. Below is the sample script:

CREATE TABLE [dbo].[StatusTable]
(
	[ID] 			[int] IDENTITY (1,1) NOT NULL,
	[TableName] 		[nvarchar] (200) NULL,
	[LoadDate] 		[datetime] NULL,
	[timestamp] 		[timestamp] NOT NULL,
	[Status] 		[int] NULL
)

Please note “timestamp” field is mandatory in this table for Power Automate flow to track the changes.

Create Custom Connector

a. Register your application with appropriate access levels. In our case the required scope for getting dataset refresh status is just “Dataset.Read.All”. Enter values as mentioned in the screenshot.

• Application Type: Server-side web application (for web apps or web APIs)
• Home Page URL: If you have any home page link then use that, else “https://login.live.com/oauth20_desktop.srf“
• Redirect URL: https://login.live.com/oauth20_desktop.srf

Once you have registered, please save the Application/ Client ID and secret. This will be used in Power Automate flow. Please note you can also use Azure portal to register an application. In next step, you can now visit Power Apps/ Power Automate to create a custom connector from scratch.

Power Apps/ Power Automate web portal > Click on Data > Custom connectors > New custom connector > Create from blank.

Enter the values as mentioned in below screenshot.

Click “Security” to move on to next tab. Enter values as shown in the image below.

• Authentication type: OAuth 2.0 (It should show OAuth section to fill below details)
• Identity Provider: Azure Active Directory
• Client Id: Enter the Application/ Client ID you after registering the application.
• Client secret: Enter the secret key.
• Login URL: You can use “https://login.windows.net”
• Tenant ID: Just enter “common”
• Resource URL: https://analysis.windows.net/powerbi/api
• Scope: Just enter “openid”
• Redirect URL: This will appear after you have created the connector. If this is not appearing, then either there is some problem from MS side, or you have not entered values correctly. I came across similar issue and after 2 days I found that MS service was having issues and they were working on it. After they fixed it, the Redirect URL appeared without any issues or changes. This was back last year April/ May.

Next click on “Definition” tab. In this tab, click “New action”. The actions created here will be executed by the connector. You can add multiple actions in this tab. Enter the values mentioned below. The Operation ID should be unique and you can name it as per the context.

Click on “Import from sample”.

• Verb – GET
• URL: https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId}/refreshes?$top={$top}

After clicking “Import”, this is how it should look.

In the next step, you can click on “Test” tab to check the working of your custom connector. In Connection, click “New connection” and enter your connection details. Enter groupId (Workspace), datasetId and number of top records.

If everything’s set-up correctly and worked as expected then you should see a green tick in Operations. Something like below image.

This completed the creation of custom connector that can be re-used for other Power Automate flows or Power Apps.

Create Power Automate Flow

In this Automated flow, whenever there is a change in the record, we will trigger Power Automate flow to refresh a Power BI dataset. The Custom Connector will be used to get the refresh status of the dataset after a certain interval. If the refresh status is completed/ failed, then it sends out email notification.

First step we need to add: SQL Server – When an item is modified (V2).

This step will trigger the flow when there is modification in the table. In this case, if there is any change in dbo.StatusTable then it will trigger the flow.

Second step: Refresh a dataset. You will need to provide the Workspace and Dataset that you want to refresh.

Third step: Initialize variable. In this step, we initialize “vStatus” variable that in the later steps will store the values for Power BI Dataset refresh status.

Fourth step: Do Until. In this step, we will execute the loop until the status is no longer “Unknown”. There will be some sub-steps in this step.

Sub-step 1: PBI – Custom connector. In this sub-step, we will pass the parameters to custom connector to get the status of latest dataset refresh. Enter group ID (Workspace ID), datasetId and value 1 for top variable.

Sub-step 2: Parse JSON. In this step, you will parse the JSON body results from previous step (i.e., PBI). Click “Generate from sample” and make sure to check the required section.

Sub-step 3: Apply to each. In this step, we will set the variable “vStatus” with new value from “Parse JSON” status.

Sub-step 4: In this step, we will add some delay so it doesn’t constantly run the REST API piece to request the status. You can also have this as the first sub-step. If the average refresh time is going to take 5 – 15 mins then it’s better to have a delay of 5 mins. This completes the sub-steps in this step.

Last step: Apply to each – Send email Notification. In this step, if the variable “status” is equal to “Completed” then it sends out Success email else sends out failure notification.

This completes the implementation of event-based dataset refresh that can also send email notification. Overall, Power Automate is a really powerful automation tool. Obviously, this implementation is basic with hard-coded values but surely it can be improved and expanded further. Let me know your comments.