Power BI - Custom Refresh with Power Automate

?? The article was co-authored with Manel Loubna OMANI

More and more customers want to have fine-grained control over Power BI Dataset Refresh. We had to recently showcase how to refresh a single table of a Power BI Dataset from a Power BI report. We thought about writing up the steps and sharing them with you in this article highlighting the main difficulties (??) encountered creating this solution.

We have used the Power Automate visual for Per BI (preview) to let the end user trigger a refresh of a specific table within a dataset using Power BI REST API without leaving the Power BI Report. The Power Automate Flow developed use enhanced refresh with the Power BI REST API leveraging built-in features for refresh management, reliability, and granularity. Some examples of what can be done using the Power BI Rest API:

• Cancel an in-progress refresh operation
• Check the status of historical, current, and pending refreshes
• Refresh individual tables and individual partitions
• Application of incremental refresh policy
• Control the refresh type for other fine-grained options
• Overpass the limitation that you have with the schedule refresh with your license (8x for Power BI Pro License, 48x for Power BI Premium and PPU)
• Dataset refreshes operations are carried out asynchronously and can be very useful for large model data refresh operations
• ...

Documentation: How to use the REST API for Power BI.

?? Chris Webb recently blogged on the topic: Calling The Power BI Enhanced Refresh API From Power Automate

Let’s see step by step how we have done…

Step 1: Create an Azure Active Directory App

In order to call Power BI APIs, we need to register a new Azure Active Directory application, for that we have followed the documentation: Register an app

??? You could also use the Power BI App Registration Tool Wizard: https://app.powerbi.com/embedsetup

?? Keep the Application (Client) ID and the Tenant ID as they will be required for the next part of this article.?

The following permissions are required:?

?? Don’t forget to Grant admin consent. This step might need the help of an Azure Active Directory Tenant Administrators (Configure how users consent to applications).

Credentials enable confidential applications to identify themselves to the authentication service when receiving tokens at a web addressable location (using an HTTPS scheme), we have created the following Client Secret:

?? Keep the Value of the Client Secret since you will only see it once and it will be required for the next part of this article.?

Step 2: Create a Flow Power Automate

Power Automate have built-in activities for Power BI, here is an illustration on how to configure a dataset refresh by using Power Automate directly within Power BI desktop:

This solution will not fit the business requirement of the customer who wants to refresh only some tables and not the entire dataset. Therefore, the use of Enhanced Refresh Power BI REST API is required and to use those advanced API a Power Automate Custom Connector will be required.

??? Power App premium license is required to be able to run the Flow and call the Power BI REST API: Types of Power Automate licenses, Power Apps and Power Automate licensing FAQs. Also, Power BI Workspace need to be on Premium or Premium per User capacity since "the refresh type need a Premium Workspace because Enhanced refresh is not supported for shared capacities" (Datasets - Refresh Dataset Limitations)

Let’s see how we can create a Custom Connector in Power Automate to refresh a dataset table with a specific type (and much more):

Start by creating a custom connector from Power BI Automate:

In “1. General” page, give a name to your custom connector (in our example we call it “RefreshSingleTable”) and a description, then select “HTTPS” Schema and put “api.powerbi.com” in the Host field

In “2. Security” page, enter the following information:

• Authentication type: OAuth 2.0
• Identity Provider: Azure Active Directory
• Client ID: Application (Client) ID (From previous steps)
• Client Secret: Client Secret (From previous steps, select the value field)
• Login URL: https://login.windows.net/
• Tenant ID: AAD Tenant ID (From previous steps)
• Resource URL: https://analysis.windows.net/powerbi/api
• Scope: https://analysis.windows.net/powerbi/api/.default
• Redirect URL: Keep it empty for now (??We will use this value at the end to update our AAD Azure application)

In “3. Definition” page we will need to describe the API’s Operations and data structure. Add a “New Action” and fill the “Summary”, “Description” and “Operation ID” then create a “Request” with this information:

• Verb: Post
• URL: https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId}/refreshes
• Header: Content-Type application/json
• Body (remove the comment):

{

"type": "Full", //specify the refresh type

"commitMode": "transactional",

"maxParallelism": 2,

"retryCount": 2,

"notifyOption": ""

"objects": [

{"table": "DimCustomer"} //specify the name of the table that you want to refresh (you can also add a partition attribute)

]

}

??In the Body definition we provided values examples in order for the Custom Connector to identify the type of the attribute (String, Integer, Object…).

Skip “4. Code (Preview)” page and test your connection in “5. Test” page:

• Create your connector
• Get the “Redirect URL” from “2. Security” page and add it in “Redirect URIs” of your App registration.
• Fill the groupId and dataserId. 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?
• Fill the Refresh type that you want (in our case, it is “Full” type, you can find more type supported here: Datasets - Refresh Dataset In Group) and all the other option for the refresh. Please note that you are not obliged to use them all in your custom connector.

Press “Test operation”.

You can download the JSON Swagger definition of this Custom Connector here: RefreshSingleTable.swagger.json

You can check the refresh history in your Power BI workspace. (PS. notice the Type of the refresh: it is Enhanced API)

Step 3: Calling the partial refresh from a Power BI Report

In Power BI Desktop:

• We have imported two different fact tables: Sales and Revenues.
• We then created the Power Automate Flow using the Custom Connector created earlier:

?? The Power BI visual only works with flows in a tenant’s default environment: Considerations and limitations

After having published the report to the Power BI service users can perform partial tables refresh by simply clicking on the Power Automate button:

?? The user running the flow must have permission to run the flow, these permissions can be granted through direct sharing by the user or to an Azure Active Directory group.?

We can also monitor the refresh history by using the Power BI Rest API with PowerShell:?

?? The Refresh Detail with RequestID will work only with API Enhanced Type refresh (Enhanced refresh with Power BI REST API).

In this article, we have seen an example of how custom Power BI refresh can be implemented to refresh a single table from a Dataset. This solution could also be used to refresh a single partition from a table, letting end users manage the refresh and to work on snapshot history of the data as long as they want, before asking for a refresh when they want to. For sure, alternatives using, for example, Azure Data Factory could also be implemented...