Synchronously Refreshing a Power BI Dataset using Azure Logic Apps

By:   |   Comments (9)   |   Related: > Azure Data Factory


I’m able to refresh a Power Dataset in the Power BI service using the Power BI API. The solution is built in an Azure Logic App. However, when the action calls the API, it immediately succeeds without giving an indication if the refresh actually succeeded or not. Is it possible to do a synchronous refresh, where the status of the refresh is returned?


Once a dataset is published to the Power BI service, you’ll typically want to refresh the dataset periodically with the latest data. An option is to trigger the refresh right after the data warehouse (or other data store) has been reloaded. This can be done through the Power BI API. The tips Refresh Power BI Dataset from Azure Data Factory - Part 1 and Refresh Power BI Dataset using Azure Logic Apps – Part 2 explain in detail how you can set up a Logic Apps custom connector to the Power BI API. This connector allows you to call the various Power BI API functions, one of them is the dataset refresh. However, if you call the API you only get a notification if the actual call to the API has succeeded or not (which is almost instantly). You do not get a notification if the dataset has actually refreshed correctly.

In this tip, we build further on the solution build in those two previous tips. We will implement a polling mechanism to verify if the dataset has refreshed successfully. If you want to follow along, please make sure the custom connector has been set up in your own environment.

Synchronously Refreshing a Dataset in Power BI

As described in the previous tips, the dataset is refreshed using the custom connector since Logic Apps didn’t support it natively. Luckily, a recent blog post on the Power BI blog announced the (preview) availability of the "Refresh a dataset" action in Microsoft Flow. Since Flow is built upon Logic Apps, this means it’s available there as well. Unfortunately, this new action is asynchronous. There is another task though in the custom connector which will assist us in retrieving the status of the dataset refresh:

return refresh history

Using this task, we can retrieve the latest refresh history of the dataset and verify if it is still running or if it has completed yet. Let’s start with a new Azure Logic App, triggered by an HTTP Request. Add a new action, search for Power BI and add the new Refresh a dataset action.

new refresh a dataset action

After adding the action, you’ll need to log into the Power BI service. Next, you can choose the workspace and the dataset from the dropdowns.

configure new action

Before we start polling for the refresh status, we’ll wait for 30 seconds, just to make sure the refresh has actually started and we’re not fetching the status of a previous refresh. This can be achieved with the Delay action, which is part of the Schedule connector.

add delay step

The configuration is straight forward: enter 30 for the count and select Second from the dropdown:

wait for 30 seconds

To have a polling mechanism, we need a loop. Inside this loop, we’ll fetch the status of the refresh and check it against some conditions. Looping in a Logic App can be achieved using the Until action. This loop will continue looping until a certain condition is met. We’re going to use a Boolean variable called FinishLoop to control the loop. Once this variable equals true, the loop can stop.

The first step is to add the action called Initialize Variable:

initialize variable action

Specify FinishLoop as the name, Boolean as the type and false as the initial value (keep in mind the value is case sensitive).

initialize variable config

Next we’re adding the Until loop, which can be found under Control.

until control action

When the loop is added, configure it to stop when FinishLoop equals true. When you click in the cell for value, you can choose the variable from the pop-up:

add finishloop variable to value

The condition should look like this:

synchronously refresh power bi dataset using azure logic apps 010

Inside the Until loop, add a new action. Search for your custom Power BI connector and the task aptly named "Returns the refresh history of the specified dataset from the specified workspace.". Since the name of an action in a Logic App can only be 80 chars longs, you need to modify the name into something shorter.

rename action

Next, add the GUID of the workspace and the dataset to the action (see the previous tips on how to find these).

configure return refresh action

To only fetch the status of the latest refresh, we need to add a parameter. Click on the cell displaying "add new parameter". This will pop-up a list of possible parameters, of which there is only one: the top parameter.

the top param

Select the parameter and specify 1 as its value.

top parameter with value 1

This action will call the Power BI API and get the most recent refresh status of the dataset. The result will be returned as an HTTP message in JSON format. You can find the exact details in the documentation. Inside the JSON body of the message, we need to extract the status field. To do this, we need to parse the JSON first, which can be done using the Parse JSON action, which can be found under Data Operations.

add parse json action

Once you click on the content cell, you can choose the result of the previous step from the list:

choose json content

We also need to specify the schema. From the documentation, I choose the sample response of a failed refresh, since this contains all possible items:

 "value": [

Click on "Use sample payload to generate schema", copy paste the JSON into the box and click on Done:

synchronously refresh power bi dataset using azure logic apps 017

In the generated schema, go to required fields list and remove endTime and serviceExceptionJson since they are both optional fields.

remove optional fields

Now we’re going to inspect the status field using a condition. Add a new step and add the Condition action which can be found under Control.

add new condition

Click on "Choose a value":

condition added

From the dynamic content list, choose the status field:

add status to condition

Since this field needs to be extracted from the parsed JSON body from the previous step, the Logic App will automatically add a for each loop to loop over all the possible fields of the JSON:

for each loop automatically added

As you can see in the screenshot, the value field (which is the parsed JSON) is used as the input for the loop. In the condition, we’re going to compare the value of the status field against the string "Failed". If the refresh has failed, we can stop the Until loop. If the condition is true, we’ll send an email saying the refresh has failed and we will set the variable FinishLoop to true. To learn more how to send an email with Logic Apps, check out the tip Azure Data Factory Pipeline Email Notification – Part 1. Setting the variable can be done with the Set Variable action. The "true" branch of the condition will look like this:

true condition for failed

If the status is not equal to "Failed", we can check if the status equals "Unknown" with another condition. The status Unknown signifies the refresh is still going on. This condition takes up the following configuration:

check for unknown

If the status is not unknown (and also not failed), the refresh has succeeded (or it is disabled but I assume you’ll want to refresh a dataset enabled for refreshing). This means the loop can stop as well. If it is equal to unknown, the loop needs to continue. First, we’ll add another delay step where we’ll wait again for 30 seconds. After that, we’re setting the variable FinishLoop to false. In the other branch of the condition (the refresh has succeeded), we’ll be setting the variable to true.

Both branches look like this:

false condition for failed, with subcondition

The entire Until loop looks like this:

until loop completed

Keep in mind the Until loop has by default a maximum of 60 iterations configured. If the refresh of your dataset takes a long time, you might want to wait longer in the delay step to avoid hitting this limit.

Now we can test the Logic App:

test logic app

In the output, we can see the first retrieved status was Unknown, which means the Until loop had to do another iteration:

status unknown

At the top of the Until loop, we can see the loop had to do 4 iterations in total.

4 iterations

The status retrieved in the final iteration was Completed. The body has then also the optional field endTime:

refresh completed

We can verify in the Power BI service that this is accurate:

power bi service check

The one-hour difference is caused by the Power BI service showing local time zones, while the API uses UTC.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, August 29, 2022 - 11:09:58 AM - Koen Verbeeck Back To Top (90423)
Hi Kevin,

it's more of a personal choice to use Logic Apps. The advantage of Logic Apps is that you don't have to write a single line of code.
The solution is (somewhat) easy to understand, which makes it easier for people who inherit it.
In other cases, I've written an Azure Function which calls the Power BI REST API to refresh the data set automatically. Just providing people with alternatives I guess :)
I'm not sure what the use case is for your PowerShell question. Make take a look at the docs for the PS cmdlets for Power BI?

Monday, August 29, 2022 - 8:46:54 AM - kevin Back To Top (90422)
Hi Koen, thanks for the article. Just want to understand, why not refresh Power BI report via Power BI service.
Another question, hoping someone can help - how to pull data into Power BI via Powershell script, is this possible ?

Friday, July 22, 2022 - 3:58:24 PM - Koen Verbeeck Back To Top (90304)
Hi Andrea,

as mentioned in the introduction, this task is implemented using a custom Logic App connector (which connects to the Power BI REST API), because the action is not available in the Logic App library. There are links to the tips that explain the set-up. An alternative is using an Azure Function, as detailed in the following blog post:


Thursday, July 21, 2022 - 6:42:07 AM - AndreaRomano Back To Top (90294)

Could you help me? I don't find this: "Returns the refresh history of the specified dataset from the specified workspace." in order to add it after "Until" step.

Friday, February 25, 2022 - 4:11:27 AM - Mubarek Back To Top (89833)
Hello Koen, thanks for this great article, I was able to create the custom connector and create the app registration on AAD, everything worked fine was able to get the last refresh history, however the next day the same logic app returned me an error with Status code: 403,
message: Access token has expired, resubmit with a new access token, Can you please help me on this. Thank you

Tuesday, May 5, 2020 - 2:04:52 AM - Bruce Canino Back To Top (85568)

Thanks for the suggestion.

I read the solution, but it not clear what need to be added. I didn't find anything with the name of the connector app.

I did find a name called Power BI Apps and added that, but I still get the Forbidden. 403 error.

Monday, May 4, 2020 - 8:02:22 AM - Koen Verbeeck Back To Top (85559)

Hi Bruce,

can you check the solution in this forum thread and see if that works?


Saturday, May 2, 2020 - 9:31:11 PM - Bruce Canino Back To Top (85548)

I went thru setting up the Custom connecter and that all seem to save fine, When I run the Returns the refresh history of the specified dataset from the specified workspace. which I shorten. It retuns Forbinen

    "statusCode": 403,
    "headers": {
        "Strict-Transport-Security": "max-age=31536000; includeSubDomains",
        "X-Frame-Options": "deny",
        "X-Content-Type-Options": "nosniff",
        "Access-Control-Expose-Headers": "RequestId",
        "RequestId": "",
        "Date": "Sun, 03 May 2020 01:27:18 GMT",
        "Content-Length": "0"

I checked the application ID and Client secert and the return URL, Is there a way to get what it is failing on?

Monday, January 13, 2020 - 3:50:34 PM - José Ambrocio Back To Top (83751)

Gracias por sus consejos

get free sql tips
agree to terms