Refresh Power BI Dataset from Report using Power Automate

As we know to refresh the Power BI dataset in Workspace. But here I’m gonna show you something different from End-User perspective.

It’s possible to refresh the dataset from the Power BI reports by Integrating Power Apps visual with Power Automate.

Use this M-Query to Find Latest Refreshed Date of your dataset:

let
Source = Table.FromRows({{Date.Year(DateTime.LocalNow())}},{“Year”}),
#”Changed Type” = Table.TransformColumnTypes(Source,{{“Year”, Int64.Type}}),
#”DST Start Date” = Table.AddColumn(#”Changed Type”, “DST StartDate”, each Date.AddDays(#date([Year],3,31),Date.DayOfWeek(#date([Year],3,31), Day.Sunday)*-1)),
#”DST End Date” = Table.AddColumn(#”DST Start Date”, “DST EndDate”, each Date.AddDays(#date([Year],10,31),Date.DayOfWeek(#date([Year],10,31), Day.Sunday)*-1)),
#”Last Refreshed UTC” = Table.AddColumn(#”DST End Date”, “Last Refreshed (UTC)”, each DateTimeZone.RemoveZone(DateTimeZone.UtcNow())),
#”Last Refreshed” = Table.AddColumn(#”Last Refreshed UTC”, “Last refreshed”, each if DateTime.Date([#”Last Refreshed (UTC)”]) > [DST StartDate] and DateTime.Date([#”Last Refreshed (UTC)”]) < [DST EndDate] then [#”Last Refreshed (UTC)”] + #duration(0,5,30,0) else [#”Last Refreshed (UTC)”] + #duration(0,5,30,0)),
#”Removed Columns” = Table.RemoveColumns(#”Last Refreshed”,{“Year”, “DST StartDate”, “DST EndDate”, “Last Refreshed (UTC)”})
in
#”Removed Columns”

Once you inserted the above query in Home -> Get data -> Blank query -> Advanced Editor. You will get the Latest Dataset Refreshed Date as below.

Refresh1new

Publish this as a dataset in the Workspace.

Open Power BI Desktop -> Connect to Published Power BI dataset. Then, Select PowerApps Visual in Visualizations Pane and Insert “Last Refreshed” in PowerApps Data from Fields Pane.

Refresh3

Click “Create New” in PowerApps Visual.

Refresh4

And Click “Go to PowerApps Studio”. It will redirect to Power Apps Studio. Now, you can create Canvas App.

Refresh5

Go to Insert Tab and Select “Button”. Enlarge the “Button” to full size and Rename it to “Refresh Dataset” as below.

Refresh6

By Clicking this button, the flow should be Triggered.

Select the “Button” and Go to Action Tab -> Power Automate -> Create a New Flow. It will redirect to Microsoft Power Automate. Select the template named Power Apps Button.

Refresh7

It looks like this.

Refresh8

Click New Step and Search “Power BI” and Select Refresh a dataset.

Refresh9 new

Now, Select the Workspace and Dataset which you published earlier using dropdown and Save the Flow.

Refresh10

Go to Power Apps Studio, Select the saved flow “Power Apps button” from the Action Tab.

Refresh11

It will automatically create Boolean Expression as PowerAppsbutton.Run().

Then, Go to File -> Save as (Save the app in the Cloud).

Finally, Open the PBIX file, the Power Apps visual will appear like this. Publish this as report in the Workspace.

Refresh12 new 2

By Clicking the Power Apps Visual in the report, it will trigger the flow. So, the dataset refresh takes place. Once the refresh complete, Latest Refresh Date and Time also shown here.

In the Scenario, End-User want to refresh the dataset manually before going through the reports. This method will be very useful and recommended.

Thanks for checking our blog. Please feel free to reach Cittabase for more information.