How to Refresh Multiple Power BI Datasets using Power Automate and the Power BI API

Leave a comment

This post comes from a question following on from my Refresh a Power BI Dataset with a Button post which I shared on LinkedIn. The question was, “I wanted to refresh more than one data set in the power bi pro”. You could do this by building out multiple buttons, or you could do this by performing multiple actions within a single flow. You could also do this by calling the Power BI API to loop through all your datasets. Let’s look at each of these.

First, let’s look at the easy option. To refresh multiple data sources from the same flow, we can add a Parallel Branch in the flow and simply call another refresh. Let’s add a Parallel Branch to our existing flow:

And search for Power BI – Refresh a dataset:

Here I have a dataset called Salesperson in my Warehouse Workspace – I’m going to select this:

Note I’m still refreshing my Sales Manager dataset:

Save and rerun the flow (or test it through the Flow designer):

We see both datasets successfully updated:

In Power BI:

Alternatively, you could call one after another, or create a brand new one if you want to split them out.

Let’s look at the API method. This can be useful if you want to loop through all your datasets without specifically selecting them.

First, create a new Power BI app in Azure.

Then, create a new Instant Flow and this time add an HTTP action:

Let’s create a new Flow and add an action, HTTP, which we will use to get a token to connect to our Power BI App:

We will provide the following:

  • Method = POST
  • URI = https://login.microsoftonline.com/common/oauth2/token
  • Headers: Content-Type = application/x-www-form-urlencoded
  • Body: Replace the bold below with your information:
    grant_type=password
    &username=your@email.com
    &password=yourpassword
    &client_id=your client id from the Azure app you create above
    &client_secret=your secret from the Azure app you created above
    &resource=https://analysis.windows.net/powerbi/api

After running this, we see we’re getting an output with our Bearer access token which we will use to connect to the API:

Now, let’s get this access token as a variable we can use. Copy the Body from above and create a new Parse JSON action, then click Generate from Sample:

Paste this into the JSON Payload sample and click Done:

We see the schema has now been built for us:

We can now use the Bearer to connect to the Power BI API. Create another HTTP action:

We will perform a simple GET operation to get Power BI datasets. The URL for this is https://api.powerbi.com/v1.0/myorg/groups/ade7c4e4-1bdd-4472-8aa5-744febb1c8e6/datasets. Note I am hardcoding the GroupId in the URL to get the datasets for a specific group (you can make your Flow as complex or simple as you like, let’s keep going). For the Headers, add:

  • Authorization – Bearer <insert the access_token dynamic content>

Now, if we run this Flow as is, we get another Body returned, with the datasets for this Power BI group. Let’s copy the body:

And create a 2nd Parse JSON action, creating the schema from the sample:

Finally, we can loop through the returned datasets to refresh. Let’s add an Apply to Each action to loop through the body:

And refresh each Power BI dataset in the Warehouse Workspace using the Ids retrieved from the API:

In running this:

We get multiple datasets refreshed in Power BI!

 

ABOUT CARL DE SOUZA

Carl de Souza is a developer and architect focusing on Microsoft Dynamics 365, Power BI, Azure, and AI.

carldesouza.comLinkedIn Twitter | YouTube

 

Leave a Reply

Your email address will not be published. Required fields are marked *