Here’s a fun trick: Using Power Automate (Previously called Microsoft Flow) to refresh a Power BI dataset via button click in a Power BI report! Let’s say that a user needs to kick off a refresh for some reason. It is sometimes difficult to explain where the user should go to manually refresh the dataset behind the report. Instead of telling them to navigate to Workspaces, then click on Datasets, find the dataset with the same name as the report, click the little refresh icon, etc… You can give them a button within the report to kick off the refresh! This trick involves using both Power Automate and another awesome technology called WrapAPI.
For this demonstration, I have connected to a web data source that refreshes virtually instantly. I then created a calculated column to provide the last refresh time to verify that method works properly. The DAX needed for the calculated column uses the UTCNOW() function and subtracts 5/24 to return Eastern time (change accordingly depending on your time zone). This logic is necessary because your Power BI Desktop client will return your current time when using the NOW() function while Power BI Service will return UTC time. The final code is as follows:
Last Refreshed = UTCNOW() – 5/24
Throwing the new column into a card and then adding a button for the eventual refresh action, our simple report looks like this:
As of right now, the button does not have an action associated with it. Publish the report to Power BI Service. This is an important step because we will need the dataset id from your Power BI portal.
Next, let’s hop over to Power Automate and set up a flow to refresh the dataset. On the left side click Create -> Automated Flow -> Skip. This will set you up with a blank flow. In the search bar at the top, search for “Http” and select When an HTTP request is received. You don’t have to change anything to the step that it creates. Simply click New step below and select Refresh a dataset with the Power BI logo. Select your workspace and dataset for the report that you just published.
Once you save the flow, click on the HTTP step to expand its details. You can see in the above screenshot that there is an “HTTP POST URL.” Click the little copy button and paste it in a browser to see its contents. This is the URL that you need to call to kick off the refresh action, BUT it needs to be a POST request. In short, if you call the URL from a web browser you are sending a GET request which will not work. In order to send a POST request, we need a bit of help from WrapAPI.
Head over to wrapapi.com and sign up for an account. They have a very generous free account which allows you call an API 30,000 times per month for free. WrapAPI is perfect for our purposes because it will allow us to create a separate API endpoint that we can trigger with a GET request that can call our Power Automate flow via a POST request!
Without further ado, create a new API by navigating to wrapapi.com/api. Copy your POST URL from Power Automate and paste it into the top URL bar. Also make sure to change the request from GET to POST!
Finally, there are four Query string parameters that we have to add to make the flow run properly. The first three will be identical to mine and the last will be specific to you. To find the fourth parameter, paste the Power Automate POST url into your browser again and copy everything after sig= at the very end of the URL. See the following screenshot for the finished solution (I’m showing my production version so I’m using a different Power Automate URL than the previous picture).
Once you’ve completed the Query strings, save your solution and click the Publish button. It will provide your final WrapAPI link that you can call at will. Copy this link and paste it in the Action field of your button in Power BI. Remember to change the action type to Web URL.
With that, you can now click on the button and kick off the WrapAPI call which will kick off the Power Automate flow which will kick off the dataset refresh!
The awesome part of this solution is that you can share the report with ANYBODY either within your organization or publicly and the link will work to refresh the dataset. Keep in mind that you are still limited to 8 refreshes per day, so use this method sparingly.
If you liked this trick, make sure to subscribe to the BI Elite Blog and YouTube channel! Happy learning!
COPPY AFTER QUESTION MARK.
In WrapAPI. After copy URL from Power Automate into WrapAPI. Instruction in WrapAPI, Copy after question mark and place it in Query String. Can’t see Question mark. Copy URL from Power Automate to note. Can now see a Question mark. Copy after Question mark and place it in URL just above Query String. Can see you have Question mark. Also tried to copy in Query String in the first four. Nothing help. Some instruction helpful.
Hello, everything after the question mark is added because of the query parameters. You’ll have to add all four query string parameters to get the full URL.
I assume this is working with database such as azure or sql server. How will it work with excel? If I keep it on one drive then will it refresh whenever the buttons hit? Please let me know, this will really ease my life and bottlenecks. Thanks in advance…
Hi Anup, my data source is actually a web api. It does not matter what your data source is in order for this to work. Power Automate/WrapAPI are simply being used to kick off a refresh on Power BI. If you have configured your data source properly on PowerBI.com with credentials and a gateway if necessary, this method will work with any data source.
I guess I followed your instructions, but each test run of the WrapAPI returns:
{
“error”: {
“code”: “AuthorizationFailed”,
“message”: “The authentication credentials are not valid.”
}
}
and my flow won’t be triggered.
Do I need a Premium Plan of Power Automate for this?
Hello Frank, yes the HTTP Request trigger of Power Automate requires a premium plan. I did not run into the error you’re showing because I am on the premium trial.
If you have a premium plan, you can schedule refresh as many times as you want.
hi
When I click on the Refresh button, it pops me to a different tab. Is there anyway to suppress that?
Hi Yevhen, I spent some time looking for a workaround but didn’t find one. Please let me know if you come up with something!
Hi i get this error.
when saving the API it asked for an API repository and endpoint name, can i just add any random name?
{
“error”: {
“code”: “MissingApiVersionParameter”,
“message”: “The api-version query parameter (?api-version=) is required for all requests.”
}
}
Hi Parker, you know there is a limitation on dataset refresh (8 times daily). does your solution support unlimited refresh rate or it’ll stop after reaching to the limit ?
Hello Parker,
While trying to save the API , I couldn’t add or select any repository and also couldn’t publish the API. I am getting following error:
1) Save this as an API endpoint
2)Publish a frozen version.
[…] refresh a dataset with a single click from a PowerApp, when an event triggers or even with a click on a button within a Power BI report! A nice solution, without the need to write a single line of code. […]
[…] users trigger the whole refresh process from a Power Automate button / link, put a refresh button right in your Power BI reports, or set up a […]
Where you say: “It will provide your final WrapAPI link that you can call at will. Copy this link….” You haven’t shown or told us where this link appears in WrapAPI. I cannot seem to find it. I attempted to call the link as shown in the web address and when my button is clicked it just opens the WrapAPI site. Further explanation is required here please?
Ignore me, I’m being nearly as slow as my PC today. – (didn’t see the video above)
Hello parker, thank you for this article, it help me so much. But, why it work with curl PHP in wrap API but it’s not working when i used curl PHP from Postman.
“HTTP Error 411. The request must be chunked or have a content length”.
This can actually be achieved without the use of a 3rd party app. Change the method to GET within the advanced options of the ‘When a HTTP request is received’ step. You can now trigger the flow by the URL within your browser. You can also pass the datasetid and groupid as query parameters on the provided URL e.g. URL&datasetid=1414&groupid=5352.
If you insert two more steps to initialise a variable for the datasetid and groupid you can pick them up by using the following: triggerOutputs()?[‘queries’][‘datasetid]… triggerOutputs()?[‘queries’][‘groupid’]. Then you can feed these variables into the workspace and dataset fields for the ‘Refresh a dataset’ step.
This makes the flow a lot more flexible and can be used across all the datasets as long as you replace the datasetid and groupid within the url for each report.