Refresh a Power BI Dataset using Microsoft Power Automate

By:   |   Updated: 2022-10-19   |   Comments   |   Related: > Power BI


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:

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.

create power bi report

In Power BI, from the All Visuals pane, I searched for "Power Automate".

add power automate visual

Now, I clicked on Add and imported this visual to the report.

add power automate visual

In the report pane, I dragged Power Automate for Power BI from the Visualizations into the report.

add power automate visual to report

Step 2: Configure Power Automate Flow

Right-click on the Power Automate visual and select the Edit option.

configure power automate visual on report

Now log into the Power Automate web interface and go to "My flows". Next, click "New" and select the "Instant cloud flow" option.

configure power automate visual on report

Below we can see the created flow on a new page. Click the "New step" option.

configure power automate visual on report

Search for "Refresh a dataset" and under Actions select "Refresh a dataset".

configure power automate visual on report

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.

configure power automate visual on report

You can see there that it has been saved and applied.

configure power automate visual on report

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.

run flow

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.

Refresh history from Power BI Service
Next Steps

Hopefully, this has been helpful and something you can implement for your Power BI reports.






get scripts

next tip button



About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

View all my tips


Article Last Updated: 2022-10-19

Comments For This Article

















get free sql tips
agree to terms