Power Automate Triggered PowerBI Dataset Refresh

[Ajh]

We've been using flow to trigger some data set refreshes and it works great, except for the fact that the action is non-blocking. Meaning the refresh is triggered successfully and the flow then moves to the next step. How do I get the power automate flow to wait for the refresh to complete, so I can perform some actions if successful and others if not successful??

Original question posted here: https://community.powerbi.com/t5/Service/Power-Automate-Triggered-PowerBI-Dataset-Refresh/m-p/208737...

[eric-cheng · Accepted solution]

Hi @Ajh  

Disclaimer - havent tried it but no reason why it shouldnt work.

1. There is an API to get all refresh history here

2. This post here refers to Logic Apps but applies to Power Automate.  You can trigger a refresh and then poll the status via the API mentioned in (1).  They created/used a custom connector as a wrapper

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

[Ajh]

Thanks, that is the route I will eventually go down I guess if no one else has anything easier. Seems like there should be a switch in the MS provided connector to allow to wait for completion or move on after triggering it, as that would obviously help way more people than just me and be far easier for the rest of us to implement.

[mtassara]

Hi AJh!
Where you able to make this work?

I'm looking to do the same thing:  Run a Power Automate flow that updates a Power Bi Model and after the Model is updated perfom some other actions (run a script on an excel file)
I looked at the post Eric mentions, but I'm not familiar at all with Azure nor Logics Apps.
So diveing into that world and see if I can create a custom trigger in Loggics Apps using Azure, I thought I'd ask if you had been able to solve this either in this way or another!

Thanks!

[Ajh]

Yes and no. I was not able to change flow to be 'blocking' like we really should be able to, but I was able to create an ugly workaround.

So I created a custom connector that is able to use the PowerBI API and run the get Dataset Refresh History command to bring back the most recent refresh results.

In my flow what I do is kick off the refresh, immediately get the history so I can record the most recent refresh's ID number, then I go into a loop where I delay 5m, then get the dataset refresh history, filter on the most recent refresh ID number, and if the status is anything other than "Unknown" it will exit the loop. This could be changed to exit the loop when "Completed" or "Cancelled" or "Errored" and maybe it should be, but it looked to me like the datasets are always 'unknown' until the attempt is completed at which point the error is thrown.

[Bruno_Andrade]

Hi @Ajh 

I'm not familiar with SQL or APIs but I was able to connect to the API and an SQL server with a on-prem gateway but I cannot replicate your flow. Can I please ask you to share the details of the SQL and filter array part?

Thanks!

[Ajh]

You don't HAVE to connect to SQL, all I'm doing there is updating a log table and saving the refresh ID number off in case I need to troubleshoot something down the line.

This is the detail of the optional sql step: 

The expression to reference the original request ID from the first green GetDatasetRefreshHistory box is:

outputs('GetDatasetRefreshHistory')?['body/value']?[0]?['requestid']

Hope that helps!

[sg123]

Hi, Sorry to bother you about this.  I'm just trying to replicate your solution.  What is the  Dataset Refresh History command please?  Or is this a bespoke command that you have written?  Many thanks

[Ajh]

When you are in flow and you click to add an action, you can search for powerBI and other stuff. The groups that these come up under are usually by 'connector'. So the PowerBI stuff all has the PowerBI logo and you will see several actions available there, by default this should be the Microsoft official connector for PowerBI. When I approached this problem, that official connector did not have an action to 'get dataset refresh history'. So whoever made this connector for Flow did not build that command into the official connector. BUT that command is available via the powerBI REST API. Since there is an API there, we can access that API using a 'custom connector' in flow. So basically I rolled my own connector (but I only have a few actions that aren't in the official connector, I didn't build in actions that already existed in the official connector). 

Here is an article that kind of describes how to roll your own connector: https://cloudatica.medium.com/power-apps-custom-connector-using-microsoft-graph-apis-d3b1d821e4fd

This is the documentation to the powerBI REST API that described the action I wanted to take: https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/get-refresh-history

I just couldn't call that action from the official connector so I had to build my own connector for that action. LOT of work around but it does work and that's what matters 🙂