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”
    • 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.