There are some reports where I have a schedule refresh but I also want to bring in new data whenever I happen to be looking at the report. Or a report where only want to refresh it on demand. This is where Power Automate can really be useful!
Before we start:
Before you do this, you will need to have created a report and published it the Power BI Service (to your "My workspace" or other created workspace). And after published, go to the Scheduled Refresh window (Workspace > Datasets and dataflows > Hover next to the dataset to see the Scheduled refresh icon) and make sure all the necessary credentials are in place for the refresh to work on the Power BI Service.
Let's get to it!
1) In Power BI Desktop, add the Power Automate custom visual from the App Source.
2) Click on the Power Automate visual to add it to the report
3) Click the "..." context menu of the Power Automate visual and choose "Edit"
4) The Power Automate should show full screen, from here click "New" then "Instant cloud flow".
5) The trigger (Power BI Button Clicked) will be added automatically, so you can focus on what you want the flow to do.
6) Click "New Step" then search for "Power BI".
7) There should be four available options, of these choose "Refresh a dataset (preview)".
8) There are 2 drop downs that need a response. First the Workspace: click the drop down and choose where you published the report. Second the Dataset: Now choose the report name from this list.
9) Save the flow.
10) Do NOT click "Back to report"! Click the back arrow to return to the flow properties screen, and here you can click "Apply" or click "Flows" to return to your list of flows. Here you can choose your flow, then click "Apply" at the top by Run or to the right of the flow.
11) Now you can click the "Back to report". :)
12) A button should be showing on your report, now you can re-size it and change the text or color.
13) Once you got it formatted, save the report. You can test your button by holding down ctrl and clicking it, but the manual refreshes will count towards your 8 daily limit in pro, or 48 daily limit in premium.
14) Publish the report, overwriting the one you published earlier. This is important because if you publish it in a new place, the button will actually refresh the other place instead!
15) Once published you can go to the report and click the button to refresh the data when you want to have it refresh.
Some things to keep in mind:
The button only reports back if it was triggered, not if the refresh was successful.
You can check that the flow ran by going to flow.microsoft.com and checking on that flow status and history.
You can check that the report actually refreshed by going to that Scheduled Refresh screen, and clicking that subtle "Refresh history" link, like usual. Right now it's showing up as blank for "Type".
I also made a video for Enterprise DNA on this!
Same content as above, but if you prefer to watch a video, that's now available.
And that's it! Hope you find this useful and have fun using Power BI!