In most cases you will want to have scheduled or event-based refresh, but there are some cases where you will want end-users to refresh the Power BI dataset. The main objective of Power Platform is to democratize the data so with Power Automate visuals integration into Power BI makes this becomes possible by giving power to the end-users. As always “With great power comes greater responsibility”. There should be Power Platform governance and best practices in place involving end-users to avoid unnecessary dataset refresh. We may also want to check or get notified who are clicking the refresh button in the report. Overall, the functionality is there so let’s start the implementation.
Assumptions
- You have basic understanding of Power Automate.
- You have Power Automate account.
- You have installed On-prem Data Gateway and added required data source to it.
- You have created a Power BI Dataset
1. Create a Power BI report
Obviously this had to be the first step. I am going to use an existing Power BI dataset “AdventureWorks” to create report. Please note it’s always a good practice to split dataset and report development.
Open Power BI Desktop. Click on “Power BI Datasets” and select the dataset to create your report.
In this report, I’m going to keep it very simple. Below is the sample report I have created using dbo.DimCustomer table that shows
– Table visual: CustomerKey, FirstName, LastName
– Count of CustomerKey
– Max of CustomerKey
2. Create Power Automate flow
Once your report is completed, add “Power Automate” visual in the report and click “Edit” in the visual’s ellipsis
You will be taken over to Power Automate portal. Click on “Instant cloud flow” to create the flow.
It should look something like below.
Now you want to find who clicked the button in Power BI report to refresh the dataset. There are multiple ways to find it out but, in this example, we are trying to send an email when a user initiates the Power BI dataset refresh from the Power Automate visual. Click “New step” and then search and select “Mail”.
Fill out like your usual email content.
– To: Email of the person/ group who will be notified.
– Subject: Dataset Refresh Initiated
– Body: Write the content and for getting the user email who initiated the refresh go to “Expression” and then under “Referencing functions” look for “triggerOutputs()”
Your Fx code should be: triggerOutputs()[‘headers’][‘x-ms-user-email’]. Overall, the email content should look something like below
Next just add “Refresh a dataset” component. You will need to enter the Workspace and Dataset name. Overall, your flow should look something like below. Click “Save” and get ready to see the awesomeness.
Adjust the size of the button in Power BI report. Your report should look like below. Click “Publish” to publish the report to Power BI web service.
3. Testing
In order to test the functionality, we will add a customer in our DimCustomer table.
Once you have added the new Customer. Click on “Run Flow” (I know I could have named it something better). The Power Automate Flow gets triggered to send the email notification and refresh Power BI dataset.
Assuming no issues and data source added to Gateway, it should refresh your dataset and show the new customer.
In addition, our flow should also show us which user triggered the Power BI dataset refresh.
Your Power Automate run history should show if it was successful or not.
If you want only specific group of end-users to run the flow then you can create a group and add the group to “Run only users” by clicking “Edit”. This should help in governance planning and some restrictions.
Conclusion
Overall, the integration of Power Automate in Power BI provides a powerful functionality to end-users. It’s also going to need a mindset shift for the leadership and development team. My take is that, use this functionality only when the use case really needs end-users to refresh the dataset. If possible restrict the groups who will be able to run/ trigger the flow. In most cases, an event-based dataset refresh should suffice. If you want to know how to implement event-based Power BI dataset refresh then please read my blog post “Event-based Power BI dataset refresh with email notification“.
I didn’t know that.
Right on my man!
Appreciate the recommendation. Let me try it out.