Using the Power BI Async REST API to refresh a partition with Power Automate (Using an HTTPS URL)
Welcome to 2022, I am looking forward to an awesome year ahead!
With the new awesome feature of now being able to use the Asynchronous refreshing with Power BI Premium/Premium per user I wanted to see if it was possible to use Power Automate to automate the process (Obviously).
My goal was to be able to refresh an individual partition of my fact table where I already had an Incremental Refresh policy defined.
To do this I wanted to make it as easy as possible for a person or process to refresh a partition with minimal effort. This would allow someone to re-process a single partition with something as simple as an HTTPS call.
Here is the documentation from Microsoft: Asynchronous refresh with the Power BI REST API – Power BI | Microsoft Docs
The good news is that I was able to get this working and you will find the details in my post below
Requirements
As always before I get down to the actual part of doing the work, there are a few requirements and details required. It is always good to follow along with a working example which I detail below.
This makes the process later a lot easier to use the details when creating the Power Automate flow.
-
The first requirement is to make sure you have got a Power BI Service Principal configured and working in your Power BI Tenant.
-
You will need the following details from your Power BI Service Principal
- In my example my AAD Security Group called “FourMoo-Service Principal”
- In my example my AAD Security Group called “FourMoo-Service Principal”
- Azure Active Directory Security Group (That your Service Principal is a part of)
- Tenant ID
- Client ID (Or App ID)
- Client Secret
-
-
Next is you will need to get the table name where you have the Incremental refresh policy defined.
- In my example I went to my table called Order
- As I have shown below it has got all the Partition Names defined in the format which is what I expect when using the Incremental Refresh Policy
- I made a note of the table name “Order”
-
The final piece of information that I need as per the Power BI Document is the Group GUID and the dataset GUID.
- I went to my PPU app workspace
- I then found my dataset as shown below.
- I then clicked on Settings and then I clicked on the dataset name
- In the URL I could then see the details I required.
- The box highlighted in PURPLE
is my Group GUID, and the box highlighted in BLUE
is the Dataset GUID - Group GUID: e67666c6-1722-4156-9643-f0e51a4e50d8
- Dataset GUID: d3c42111-df45-4283-ae0d-bc5498f3abcd
- Now I had got all the details I required to start putting everything together.
Power BI App Workspace requirements
I had to ensure that my Power BI Service Principal account had access to the App Workspace.
I went to my App Workspace and added the account as shown below.
Automating the Async with Power Automate
Yes, you have guessed it, this is the final piece where I now take all the details I gathered earlier and put this together in a Power Automate flow.
The awesome thing about Power Automate is that I can export the flow, so if you are following along, you can download a copy of the Power Automate flow at the end of this blog post.
That also means I only need to show you how to update the configuration settings in the Process Partition part of the flow.
- This is what the overall flow looks like.
- I then clicked on the Process Partition to expand it.
- As shown below all the purple boxes is where I need to put in the details
-
Starting with Number 1, this is the GROUP GUID
- With my example this is: e67666c6-1722-4156-9643-f0e51a4e50d8
-
Next, is number 2, this is the DATASET GUID
- With my example this is: d3c42111-df45-4283-ae0d-bc5498f3abcd
-
Number 3, this is the table name
- With my example this is: Order
- Number 4, this is the Tenant ID
- Number 5, this is the Client ID or App ID
- And number 6 is the Client Secret for the Client ID / App ID
- This is what it looks like once it is all filled in
- I then click on Save in my Flow.
-
NOTE: Once I have saved my flow, I need to go back to the settings and make sure that the flow is turned on as shown below.
How to use the Power Automate HTTPS request
Now is the fun part to see it working in action.
- To do this I have to go back into my flow and get the HTTPS link.
- I click on When a HTTP request is received and click on the Copy Url as shown below.
-
Below is what the HTTPS URL looks like
https://prod-20.australiasoutheast.logic.azure.com/workflows/978853752b5e4823a22ec2d94fcb222c/triggers/manual/paths/invoke/partitionkey1/{partitionkey1}?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=YbkD-Szms3Kf3K19PsV0dqz1LZv_dK7BEI3BnNpGKIw
-
In the above URL what I need to do is to put in the partition name that I want to process.
- As shown below these are my partition names on my order table.
- Because I have configured my Incremental Refresh Policy as Monthly my partition names are in the monthly format of YYYYQQMM
- For my example I want to process the partition 2022Q101
-
I then update my HTTPS URL with the following highlighted in BLUE below
https://prod-20.australiasoutheast.logic.azure.com/workflows/978853752b5e4823a22ec2d94fcb222c/triggers/manual/paths/invoke/partitionkey1/2022Q101?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=YbkD-Szms3Kf3K19PsV0dqz1LZv_dK7BEI3BnNpGKIw
-
I then take this URL and put it into a browser URL and click on Go
- Once this has completed, I see the following in the browser window, which shown me the PartitionKey
- I then go back to my flow and I can then verify that the flow ran successfully
-
The final step is to then check on my Power BI Dataset to see if it successfully has been refreshed.
- I go into the dataset and then click on the Refresh History.
- I can then see that the refresh was completed successfully
-
Just to confirm 100% I went into SSMS, then to the table and partitions and I can verify below that the Last Processed date matches the refresh history.
- As you can see it successfully refreshed.
- In closing what I would now do is to send the HTTPS URL to the end user or process that wants to refresh a partition, explaining how the partition names work and they will then be able to successfully process a single partition.
Additional details on the Async Refresh Body for tables where an Incremental Refresh Policy has been defined.
What I wanted to also explain is how to refresh a table with partitions, where I had the incremental refresh policy defined in Power BI desktop.
In my example the BODY contained the following XMLA syntax
{ "type": "full", "commitMode": "transactional", "objects": [ { "table": "Order", "partition": "2022Q101" } ], "applyRefreshPolicy": "false" }
For this table to be processed successfully I had to put in the following below:
“commitMode”: “transactional”,
“applyRefreshPolicy”: “false”
The reason for this is because the incremental policy is defined, I need to set the RefreshPolicy to false when processing a table with incremental refresh policy enabled tables.
If I wanted to process a single table, which had no partitions defined I would use the following syntax in the body section of my flow, with my table name being called “City”
{ "type": "full", "objects": [ { "table": "City" } ] }
UPDATE: 2022-01-24
If I wanted to do a FULL process on a table with partitions I would use the following in the JSON Body below
{ "type": "full", "commitMode": "transactional", "objects": [ { "table": "Order" } ], "applyRefreshPolicy": "false" }
Yes, the above code would simply ignore the partitionkey that is inputted in the URL.
Summary
In this rather longer blog post, I have shown how I have used the new Power BI Async REST API and combination of Power Automate to successfully refresh a single partition.
I have also shown that for the end user to then invoke the processing of a single partition all that they need is the URL and to change the PartitionKey to the corresponding partition name to successfully process the partition.
Thanks for reading, I hope you found this useful and if you have any questions, please leave them in the comments section below.
As promised here is the Power Automate Package to process Partition: Power Automate HTTP-ProcessPBIPremiumPPUPartitionAsync_FOURMOO.zip
- What you can do is to import the above ZIP file in Power Automate to get the required steps already created for you.
Great post!
One question, what would the JSON body look like if I wanted to refresh an entire dataset but some of the tables have incremental refresh defined and I won’t know which partition I want to refresh necessarily.
Basically I want to mimic the scheduled refresh command from the Power BI service but do it through HTTP after some upstream processing is complete and respect the incremental refresh policies.
Would the below work?
{
“type”:”full”,
“commitMode”: “transactional”,
“objects”:[
{
“database”:”MyModelName”
}
],
“applyRefreshPolicy”: “true”
}
Hi Jordan,
Thanks for the question, there is some good news.
All that you need to do is to use the following syntax below. I did test this and it will refresh all the tables as well as if a table has got an incremental refresh policy it will ONLY refresh those partitions.
{
"refresh": {
"type": "full",
"objects": [
{
"database": "MyModelName"
}
]
}
}
Thanks so much!
No worries, happy to help.
Excellent, thank you!
I am looking for a way to schedule refresh on custom partitions I’ve created without using Power Automate.
Can I set some property of certain partitions so that scheduled refresh will skip it and only refresh some of the partition.
Similar to what it is doing with incremental refresh, but without date/time logic.
Hi Michael,
Firstly the way the incremental partitions are created are always with the same naming format.
You can certainly skip certain partitions as long as you send through the partition name for the partitions you want to refresh.
For example if you wanted to process data for 2022-06-10 and 2022-06-14, you would then pass through 2022Q20610 and 2022Q20614
Does that make sense?
Wonderful guide to the enhanced dataset refresh web call.
Hi Matthew,
Thanks for comment and glad I could help!
Hi, could you able to tell me how to generate that HHTP links i just stuck there i don’t know how to generate it ?
Hi Kannan,
You need to first save the Power Automat Flow, then go back in and you will then get the option to see the HTTP request?
Great blog!
Question – I get duplicate rows when I refresh using this method. I am currently using Postman to create a proof of concept and I get duplicate rows within PowerBi even though my data source in Redshift has 1 row. Any idea why that would be happening?
Thanks for the kind words!
I am not sure why you are getting multiple duplicate rows, it is working well when I am using it. Possibly check your Power Automate step to make sure it is working if you have to hard-code the values?
Thanks Gilbert for this information. Can you please help me on where can I get the Client ID or App ID and Client Secret for the Client ID / App ID?
Hi Akhil,
You can follow these steps from PowerBI.Tips in the pre-Requisites section on how to create and assign the service principal
Power BI Scanner – Pre-Requisites