Unanswered

Refresh a Dataset after multiple Dataflow refreshes

(1) Share

Report

Posted on by cbschley

I want to refresh a dataset after checking if multiple Dataflows have completed.

In the UI, I start with checking "When a dataflow refresh completes" and proceed to build the flow. The tricky part is, I also want it to check two other dataflows for a recent refresh, and if so, trigger a dataset refresh. I don't see a very intuitive way to do this. It seems like the way PowerAutomate wants me to do this is to trigger another Dataflow Refresh upon completion of the last Dataflow. The problem with this is that re-use dataflows for multiple dashboards, so I don't want to keep refreshing them in series, I simply want to check if the refresh was completed or not via scheduled refresh in Power BI.

Categories:

Using flows

I have the same question (0)

All responses (5)

Answers (0)

wskinnermctc 6,519 Moderator on at

Like (0)

Report

Could you add a column into your dataset that has like the current time whenever it is refreshed. Then you can use that column to check? Kind of bootleg work around, but it should work.

I have columns in my dataverse tables and SharePoint lists that are called "QueryLastRun" that is a column I also make in the SQL server. So whenever my data gets loaded or refreshed, the "QueryLastRun" is updated with a value of the date time from SQL server.
So I'm able to look at a table or list and see when the last time this row/data was refreshed.

Was this reply helpful? Yes No
cbschley 12 on at

Like (1)

Report

I'm not quite understanding your suggestion. Are you saying to add a column in each table that references the last time its associated Dataflow was successfully ran? If so, I'm not sure how I would use PowerAutomate to build a flow based off of that.

Essentially I have something like this:
Dataflow1
Dataflow2
Dataflow3
Dataset1

All three Dataflows help to generate Dataset1. I want to refresh Dataset1 once all three Dataflows successfully complete.

Was this reply helpful? Yes No
brunoctesser 2 on at

Like (0)

Report

Have you been able to solve this issue? I have the exact same problem and I can't figure it out.

Was this reply helpful? Yes No
cbschley 12 on at

Like (0)

Report

Hi @brunoctesser yes I was able to figure it out, although I don't think it is perfectly smooth solution.

Basically it goes like this. Lets say you use my example above where you have 3 different dataflows feeding 1 dataset. I set up an Excel document that contains 3 tables (one for each dataflow). Then, in PowerAutomate, I have a flow that adds a new row of data to each Excel table after each dataflow completes a refresh. I use a series of variables in PowerAutomate that start at 0, and when each table in Excel has >= 1 row, then I kick off my DataSet refresh. After the refresh, I have the flow set the variables back to 0 and delete all rows in my Excel tables so that the process can start fresh next time. Let me know if you need any clarity there.

I will also say that PowerAutomate now has CoPilot integrated, so you may be able to have that tool write out a flow that is much more performant than what I described above. A few co-workers of mine have done so successfully, but I haven't gotten around to testing that out.

Was this reply helpful? Yes No
einld 2 on at

Like (0)

Report

I also would like to know if there is an easier way than using Excel/Power Automate - I googled a bit and thought I found the answer with "use a flow to perform a changeset request in Dataverse" but I was wrong. The concept is what I want for Dataflow though.
Same as you, I had to think of a long-winded way - which was;
1. Create an excel spreadsheet saved in Sharepoint that records the refresh history of dataflow (make the data into a "table")
2. Create a flow that would add the refresh history of the dataflows
2. In excel, have a formula that does the count (based off name of dataflows & conditions for dataflow like 'success' or refresh type = 'ViaApi' so that it won't kick it off if someone manual refreshes one dataflow)
3. Create a flow based on recurrence (ie., check every 15minutes or depending on how long your flow takes to finish - should give you an estimate of when you should trigger the time) that will check the last row in the spreadsheet (so that your flow isn't doing a for each loop) and if that formula is 4 (say for example there's four dataflows) then trigger the refresh on dataset. Then this last step (which I haven't quite fixed) is to ensure your formula updates to 5 so that if 15mins later the count is still 4 you don't want to refresh dataset again....OR you could have an action that deletes those rows so that it won't trigger the dataset refresh again.

In the whole solution above I have to make sure to convert the timestamps to local time (so that dataflows refresh only happens on weekdays etc.).

There must be a better solution out there right???

Was this reply helpful? Yes No