You can add a button in your report which will refresh the data once you click on the refresh button in the report view. I have seen in various forums that this has been asked by many clients. For achieving this functionality we will use Microsoft Power Automate and also use Wrap API.
I hope that this article will help you get more insight into using Power Automate and Power BI in a combination. Also, using the refresh button in the report helps to save the limited refresh capability i.e 8 times/day for pro users and 48 times for Power BI Premium users. So whenever the user checks the report, he/she can refresh the report with a click of a button.
So let`s check How to refresh the dataset using a button in Report View of PowerBI? In order to refresh the dataset using the button in Power BI, We have to configure Power Automate, Wrap API, Action property of the button in Power BI, and personal gateway need to be setup. Check out the steps below and you can also do the same if you follow the below steps.
Step1: Download the Personal Data Gateway on your Machine from app.powerbi.com. While installing select the personal mode as you will be installing the gateway on your machine.
Step2: Once Installed on your machine, you have entered the email which is the same as the email id entered in Power Service.
So with this, your personal gateway in your machine is set and running. Make sure if you have a scheduled refresh setup then at that moment of time your machine should be up and running.
Step3: Now, We will be using the Sample Superstore Dataset which you can download from here.
Click on the Get Data >> Excel option to import the dataset into Power BI.
Step4: Create a Last Refresh Measure which will get show the Date and Time at which dataset was last refreshed. See the below dax expression for the Last Refreshed measure.
Last Refreshed =
Place the measure in a card so that we can see the date and time at which the dataset gets refreshed.
Step5: Create a button and change the button text to “Refresh Button”.
Step6: Publish the report in a workspace.
Step7: Now, We will be doing configuration in the Microsoft Power Automate. Log in to flow.microsoft.com, click on the create button on the left pane, then click on automated flow. Automated flow work on the triggering event. Related Post: Send an email alert when a new PDF file gets created in the SharePoint folder using Power Automate
- In the connector search option, search for HTTP, In the triggers, look for “When the HTTP request is received”. Once you click on the trigger, you will get the box as shown below.
- Click on the next step button and then search for “PowerBI” and click on the action “Refresh a Dataset”
- Once you click on Refresh a dataset (preview), you will be asked to specify the Workspace and Dataset. Then click on the Save button.
- After you click on the save button, you will be able to see the HTTP POST URL in the box “When the HTTP request is received “. Copy the URL from the box.
Step8: Put the saved URL in the browser in order to check the Key and its value. You will have to enter the key and value in the Wrap API.
Step9: Open the WrapApi Website. Register on the website with your details. With Wrap API, you will be able to make 30000 requests per month. However, since we are using Power BI so we can make 8 refreshes per day for normal users and 48 refreshes per day for Premium users. Wrap API will wrap the HTTP request that we will send at the press of a button in power bi. Let`s now see the configuration we need to do in Wrap API.
- Once you have logged in to the Wrap API site, click on the “Try building an API” button.
- Change the Request from Get to Post and enter the URL which we have copied previously for Power Automate.
- As mention in the 8th step, We will copy the key and their value from the URL which we have pasted in the browser just to do copy and paste easily. After entering all the key-value pairs, Save the API.
- When you click on the save button, you will have to create or an existing Repository and will have to give an endpoint name. You can give any name here.
- Once you click on the save button, you will notice that you will have the option to publish the API. So click on the Publish button.
- After Publishing click on the Run Button.
- Once your run is complete, click on the use button placed on the top right.
- Here, you will get the link which you need to place in the action property of the button so that on the click of the button the URL request will hit the flow in Microsoft Power Automate which will eventually refresh the dataset in Power BI Service.
Step10: Click on the Refresh button in Power BI Desktop which we have created in step 5. Go to the visualization pane and enable the Action Property and select Type as Web URL and then in the Web URL paste the API URL received from the previous step.
Step11: Publish the report once again after making the changes in the above step. Replace the same file in the same workspace as we have mentioned in the Microsoft Flow. Step12: Go to My Workspace and go to the dataset. Right Click on it and go to settings and check
the personal gateway is configured correctly or not.
Click on the Gateway Connection and you should be able to see the status as running. See the below screenshot for more detail.
Step13: Now we will check whether the configuration which we have done in Power BI Desktop, Wrap API and Microsoft Power Automate is correct and we are getting the expected result or not.
So we have inserted a row with dummy data.
Step14: In the report which you have created for the demo, edit the pbix file in Power BI Service. Create a table and place Row id as a column to check whether data is getting refreshed in the Power BI Service or not.
Step15: Save the PBIX file first then Click on the refresh button and then refresh the browser. You will be able to see that Row ID value 0 is appended into the table.
Also, the tab will get opened in the browser in which “success”: true will be mentioned.
I hope you must have learned about Power Automate (Microsoft Flow), Wrap API, Power BI Service, and Personal Gateway Connection. Refreshing dataset manually gives power to the user to see the updated data whenever they see the report. I request you share your feedback through comments so that I can bring better content in the upcoming post. Thank you.