So far in this series (see part 1, part 2 and part 3) I’ve looked at how you can create a Power Automate custom connector that uses the Power BI Enhanced Refresh API to kick off a dataset refresh. That’s only half the story though: once the refresh has been started you need to know if it has finished and, if so, whether it finished successfully or not. In this post I’ll show how to do this.
When you start a refresh using the Enhanced Refresh API Power BI returns a unique identifier for that refresh operation and you will need to modify your Power Automate custom connector to make it easy to capture and use that identifier. You can do this on the Definition stage of the custom connector wizard for an Action that kicks off a refresh (ie any of the Actions I built in the previous posts in this series) by going to the Response section, clicking on the Add default response button and pasting the following sample response (from here in the docs) into the Headers box and clicking Import:
x-ms-request-id: 87f31ef7-1e3a-4006-9b0b-191693e79e9e
Location: https://api.powerbi.com/v1.0/myorg/groups/f089354e-8366-4e18-aea3-4cb4a3a50b48/datasets/cfafbeb1-8037-4d0c-896e-a46fb27ff229/refreshes/87f31ef7-1e3a-4006-9b0b-191693e79e9e
It’s the x-ms-request-id custom header that will contain the unique identifier for the refresh.
The next step is to create a new Action to check the status of the refresh using the Get Refresh Execution Details In Group API endpoint. To do this, click on the New action button on the Definition stage of the custom connector wizard and fill in the details in the General section:
Then fill in the Request section. Using Import from sample, select the verb GET, enter the following URL:
https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId}/refreshes/{refreshId}
Then in the Response section click Add default response and paste in the (rather long) sample JSON response from the docs found here. The response should look like this:
You can now use this new Action in a Flow. Here’s a simple example:
The first two Actions here, “Manually trigger a flow” and “Incremental Refresh”, kick off a refresh in the way I’ve shown in the previous posts in this series. The “Do until” Action is where the interesting stuff happens:
What this does is:
- First of all, the Delay Action waits for 30 seconds. There’s no point checking the status of a refresh immediately after it has been started, and in this case 30 seconds is a reasonable amount of time to wait. Depending on how long your refresh takes you may want to use a longer delay.
- Then call the new Action created above to check the status of the refresh that has just been started, using the x-ms-request-id value returned by the Incremental Refresh Action.
- If the status (returned by the Get status of one dataset refresh Action) is not Unknown then the refresh has completed and the Do until loop can be terminated. If the status is Unknown then the refresh has either not started or is still in progress so the loop should be run again, ie it will wait for another 30 seconds and then check the status again.
Finally, once the refresh has completed the Condition Action sends an email telling me the outcome: if the status is Completed then the refresh has succeeded; if it is not then the status tells you at a high-level what went wrong (you can look at the extendedStatus and the contents of the objects JSON array for more details).
This series has been so incredibly valuable. Thank you! I’m missing one piece on this step. I’m not seeing where “status” is available as a parameter from the “Incremental Refresh” Action in the Do Until Step. The only parameters I see are x-ms-request-id, Location, Headers and Body. I see it as a parameter from the “get status from one dataset refresh”, but since this Action does not happen before Do Until, Its not an optional parameter. Based off this description, are you able to tell what I’ve done wrong? Thank you!
Hi! you have to do apply the Delay and Get status of one dataset refresh first and the status of the later will appear on the list to be picked.
This is great solution Chris. I am falling into one trouble, I tried using Status “Unknown” and Extended status “InProgress” in Do until followed by 20 min delay. But post Do until commits the condition to evaluate status of refresh always tells me false even if refresh was success. In condition too I tried extended status and status too.
Any advise here please?