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.

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.

Click “Create New” in PowerApps Visual.

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

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

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.

It looks like this.

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

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

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

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.

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.