Power Automate · Power BI · Refresh

Calling The Power BI Enhanced Refresh API From Power Automate, Part 1: Creating A Basic Custom Connector

I love the new Power BI Enhanced Refresh API: it allows you to do things like refresh individual tables in your dataset, override incremental refresh policies, control the amount of parallelism, cancel refreshes and a lot more, while being easier to use than the XMLA Endpoint. However, like the XMLA Endpoint, one problem remains: how can you schedule a dataset refresh using it? One option is to create a custom connector for Power Automate (similar to what I described here for the Export API, before the Power BI export actions for Power Automate had been released): this not only allows you to schedule more complex refreshes but also gives you more flexibility over scheduling and do things like send emails if refreshes fail.

There’s no point going into the details of creating a custom connector for a Power BI API endpoint because it’s been done before, most notably by Konstantinos Ioannou who has a very detailed walkthrough here which I strongly suggest you read. There’s only one thing that has changed since he wrote that post: the Power BI App Registration Tool is now here. You also need to give the app you create the “Read and write all datasets” permission:

When you get to the Definition stage of creating the connector there are some choices to make. The Enhanced Refresh API has a lot of functionality and it could be very complicated to build a custom connector that supports everything – especially if you or your users don’t need all that functionality, or if a lot of options could confused your users. As a result it could be better to only expose a subset of the functionality – and that’s what I’ll do in this first post.

Let’s take a few basic options to start off with: the refresh type (ie do you want to do a full refresh, clear the data out of the dataset etc?), the commit mode (do you want everything you’re refreshing to be refreshed in single transaction?), the maximum amount of parallelism and the number of retries if refresh fails. Click on the New Action button and fill in the details in the General section:

Then, in the Request section, click on Import from sample and select the verb POST, enter the following URL:
https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId}/refreshes

…leave the Headers box empty and then enter the following in the Body box:

{
    "type": "Full",
    "commitMode": "transactional",
    "maxParallelism": 2,
    "retryCount": 2
}

This will create an Action that allows you to set the type, commitMode, maxParallelism and retryCount options. The Request section of the Definition step should look like this:

You can finish creating the connector as per Konstantinos’s instructions after that.

Finally, to test this connector in a flow, you can build a new instant cloud flow that looks like this:

[You can find the workspace ID (the groupId for the API) and the dataset ID by going to the dataset’s Settings page in the Power BI Service and getting them from the URL as detailed here]

You will also need to turn off the Asynchronous Pattern option in the Settings dialog of the action:

You should now have a flow which can kick off a dataset refresh with a few options. This is only the beginning though: there are more options that can be added, and this flow only starts a refresh – it doesn’t tell you whether the refresh succeeded or not, or allow you to cancel a refresh, or anything else fun like that. We’ll investigate all of these things and more in future posts in this series.

9 thoughts on “Calling The Power BI Enhanced Refresh API From Power Automate, Part 1: Creating A Basic Custom Connector

  1. While it is relatively easy to implement polling for the end date of the most recent refresh and then returning the completion status – it would be much nicer if we had feature parity with dataflows. Although one has to wonder how it is implemented behind the scenes. I think whatever is labeled “event triggered” in Power Automate is most likely based on incessant polling anyway.

  2. Thanks Chris this is helpful, I tried similar flow and it’s running successful but in actual not triggering dataset to refresh.

    Raw output –
    {
    “statusCode”: 200,
    “headers”: {
    “Transfer-Encoding”: “chunked”,
    “Vary”: “Accept-Encoding”,
    “Request-Context”: “appId=cid-v1:132fdd14-1f8c-4064-*******”,
    “x-ms-function-status”: “OK”,
    “Date”: “Mon, 12 Sep 2022 07:30:22 GMT”,
    “Content-Type”: “application/json; charset=utf-8”,
    “Content-Length”: “26”
    },
    “body”: {
    “message”: “Hello World”
    }
    }

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Is the dataset you’re trying to refresh in a Premium capacity? If so, does the API call you’re trying to make work when you try it in another tool like Postman?

  3. Hello Chris,
    I want to auto retry of powerbi datasets refresh from a workspace, not individual datasets but all datasets in workspace which has status “Failed”
    So, If I have 15 datasets in a one workspace and 5 datasets gets refresh failed then all 3 datasets should automatically retry refresh.
    Can you please help in this?

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.