Problem
Power BI reports are great, but often the dataset is not current and therefore doesn’t provide the most accurate picture. In this article, we will look at how to use Power Automate to refresh a dataset used in a Power BI report.
Solution
In Power BI, refreshing data means importing the data from the original data sources into a dataset based on the invoke refresh schedule or done on-demand. We can perform multiple dataset refreshes based on business needs if the underlying data source data changes frequently.
In Power BI, a data refresh is dependent on the configured storage modes and dataset types. If we go look at the storage modes, import mode, direct query mode, live connect mode, and push mode comes into the picture. In Power BI, a refresh option can consist of multiple refresh types like a data refresh, OneDrive refresh, refresh of query caches, tile refresh, and a refresh of report visuals.
Several options are available to refresh the dataset in a Power BI report. With a manual refresh, the data option is available on the Power BI service. However, the Power BI Service provides a refresh right now for a dataset. A data refresh can be scheduled with Power BI shared capacity subscriptions, but there are limited refreshes that can occur per day.
The Power BI documentation indicates that "On demand refreshes are not included in the refresh limitation." If the required number of refreshes exceeds the limit per business needs, we need to use on-demand refreshes triggered by another system. Depending on business needs, there are numerous dataset refresh methods, as shown below:
- Specifically, we can add a refresh dataset action to the Power BI connector for Microsoft flow. Now, you will be able to trigger dataset refreshes based on hundreds of flow triggers.
- Refresh a dataset stored on OneDrive or SharePoint Online.
- Refresh a Power BI dataset by PowerShell.
- Refresh data option in Power BI embedded.
- Advanced incremental refresh and real-time data with the XMLA endpoint.
- Microsoft Power Automate service also supports refreshing the Power BI dataset.
We can opt for any option based on the use case to refresh the dataset frequency, but we will look at Power Automate for this article.
Refresh Power BI Dataset with Power Automate
Based on the specific use case, I opted to use Power Automate. Power Automate is a great tool to start on-demand dataset refreshes. From October 2019 onwards, Microsoft added a new action to the Power BI connector called "Refresh a dataset," which will start a dataset refresh. Adding a Power BI Automate button is a great approach to kick off a flow that will call the action. This is an asynchronous process, so a dataset may take a few minutes to refresh.
For this tip, the recommended approach of refreshing data through Power Automate on the report side was chosen for the following reasons:
- In the workspace, users should not fall into the category of admin or contributor. However, the user needs report data refresh options.
- Data refresh rights should be required for the report viewer.
- Data refresh frequency is also higher than scheduled refresh options.
The following will demonstrate the steps to refresh a Power BI report using Power Automate from the report side.
I used Power BI Desktop to develop a report and I used my Office 365 subscription to login to Power BI Desktop.
I created a report and published it to my local workspace using "SkillSet" as the PBX filename. We will see how we can build a process to refresh this dataset using Power Automate.
Step 1: Import Power Automate into Power BI Report
I created a new report to integrate Power Automate for the dataset refresh. To do so, I clicked get more visuals, available inside the Build Visual in the Visualizations Tab.
Note: It is possible to integrate Power Automate into an existing report once it has been published, but we will look at creating a new report just to do the refresh.
It is recommended to publish the report before moving to the next step.
In Power BI, from the All Visuals pane, I searched for "Power Automate".
Now, I clicked on Add and imported this visual to the report.
In the report pane, I dragged Power Automate for Power BI from the Visualizations into the report.
Step 2: Configure Power Automate Flow
Right-click on the Power Automate visual and select the Edit option.
Now log into the Power Automate web interface and go to "My flows". Next, click "New" and select the "Instant cloud flow" option.
Below we can see the created flow on a new page. Click the "New step" option.
Search for "Refresh a dataset" and under Actions select "Refresh a dataset".
Step 3: Configure Refresh a Dataset Button in Power Automate
The respective flow actions were successful. Select the workspace or user and the targeted dataset in the respective fields. For this tip, I used "My Workspace" and the "SkillSet" dataset. However, depending on your system’s setup, Power Automate may ask you to sign in to Power BI first. I was already signed in, so you may need to provide further credentials. Next, click Save.
You can see there that it has been saved and applied.
After we perform the above steps, the report will have the button "Run flow" as shown below. We can customize the button, but I will leave as is for now.
Review Refresh History
After I clicked the Run Flow button, I opened the Power BI Service and checked the Refresh History for the "Skillset" dataset. Below we can see when it was a scheduled run versus an on-demand run and also the current status which shows "In Progress" for this current refresh we invoked with Power Automate.
Next Steps
Hopefully, this has been helpful and something you can implement for your Power BI reports.
- Related Posts:
- Learning SQL Server Power BI Tips
- Download Power BI tools and apps
- Power BI Documentation for more learning
- Incremental Refresh and real-time data for datasets.
