Kick your Power BI dashboards up a notch by using Power Automate to dynamically refresh data sources. While the Power BI Service requires users to dictate a set refresh schedule, developers can use Power Automate to tell Power BI to refresh a data source based on an event.
The Power BI Service at powerbi.com only allows you to define specific times to refresh a data source. At times it can even be cumbersome to add a row for each time you want to start an update.
Here are some example scenarios, where you may want to use Power Automate to update a Power BI Dataset
- When files are added or updated on SharePoint
- When a SharePoint List or Microsoft List is updated
- Defined Refresh Intervals – Every 15min, every 2 hours, every Tuesday at 2PM etc.
Power Automate allows you a lot more control over the Power BI refresh process.
Within some limitations.
Let’s take a look.
What is Power Automate?
Microsoft Power Automate is a service from Microsoft that helps users create automated workflows between apps and services to synchronize files, get notifications, collect data, and more. It enables businesses to automate routine tasks and processes, improving efficiency and productivity.
Power Automate Desktop focuses on automating tasks on a local computer, allowing the creation of flows that interact with desktop applications and web browsers. It provides a visual designer to create automation without coding.
Power Automate Flows, on the other hand, is cloud-based and enables the creation of workflows between various online services and applications. It connects different cloud platforms and allows for more complex logic and conditions.
Power Automate is included in many enterprise levels of Microsoft 365 subscriptions. If you work at a moderate sized business, it’s likely that you already have access to it.
It is included in the following office tiers.
- Office 365 E1
- Office 365 E3
- Office 365 E5
If you don’t have access to it, reach out to your IT department to request access. Pay as you go and individual licenses are also available outside of Microsoft 365 subscriptions.
Note: Microsoft 365 is a re-brand of Microsoft Office 365 that most people are already familiar with.
How to Automate Power BI Data Refreshes
Before getting started there are a couple of pre-requisites.
- Publish a Power BI dataset to the Power BI Service and place it in the workspace that it’s going to be viewed from. Power Automate will need a path to locate the Power BI dataset.
- Ensure that the Power Automate account has access to the Power BI Workspace. This usually isn’t a problem if you’ve logged into both services using the same Microsoft account.
Now that the pre-requisites are out of the way, launch Power Automate.
Go to: make.powerautomate.com
This is where things get kind of cool!
Microsoft has been integrated ChatGPT into most of its products under the name brand Copilot.
You can either create a workflow from scratch, or you can describe a process and Power Automate will walk you through it. We’ll set this up both ways.
Using Microsoft Copilot to Quickly Build a Power Automate Flow
As you type into the chat box, Copilot will suggest workflows for you. You can either use one of its suggestions or move forward with a custom one that you’ve typed in.
Note: Copilot works best for more common use cases. It relies on training data from Power Automate users who have opted into sharing their information. Copilot currently seems to struggle a bit with more niche requests.
The next page will prompt you to connect to a SharePoint site and to a Power BI Workspace and Dataset.
After pressing Next, Power Automate will generate a workflow for you using the latest Power Automate experience with Copilot integration. The idea is that you can use the Copilot bar on the right to enter requests into a chat box and Power Automate will update your workflow on command.
It’s an interesting take on Robotic Process Automation (RPA) but in our testing it isn’t quite as useful as we would hope.
This style of AI interface is likely the future for a good number of products coming down the pipeline, not just from Microsoft. It’s good to be aware of these tools and to keep an eye on them in the future. There could be a point where knowing how to describe a process is more lucrative than being able to create one yourself!
Automatically Refreshing a Power BI Dataset using the Power Automate Flow Editor
If you’re old fashioned and not quite ready to trust AI to do all of your work for you, you can setup Power Automate flows manually.
From the Power Automate Website follow these instructions.
Step 1.) Click on Create
Step 2.) Select Automated Cloud Flow or Scheduled Cloud Flow
Automated Cloud Flows use triggers to tell Power Automate to start refreshing Power BI. These are the items like a new file is created on SharePoint. Schedule flows run at specific times of data or time intervals.
Step 3.) Select Automated Cloud Flow or Scheduled Cloud Flow
Step 4.) Name your Workflow and Search for a Trigger or Event
The more descriptive name you use in this step the better. If you have multiple workflows being refreshed dynamically, you will need to be able to identify them in the future if a workflow needs to be updated.
Our example will use SharePoint Files, but there are events for almost anything. Be creative! You could even kick off a Power BI refresh based on a when an e-mail is sent for those times when you’re out of town!
After you click on Create, You will be taken to a screen that prompts you to enter in your SharePoint URL and the folder or list that you want it to look at.
Step 5.) Click on + Next Step
This is the button that you use to create a sequence in an automation flow. Power Automate works top to bottom step by step.
The plus New Step creates the next step in teh sequence.
Step 6.) Refresh a Power BI Dataset
Step 7.) Update it with your specific Workspace and Dataset to refresh.
Step 8.) Click Save
At this point, your Power Automate Workflow will be active.
If a file is uploaded to the SharePoint folder that the workflow is point towards, it will tell Power BI to begin refreshing the data.
To ensure that your Power Automate refresh is working, you can click on Test in the top right corner. This will tell Power Automate to watch for an updated file to occur and will begin logging the workflow action for you to review.
After you click on Test, upload a file to SharePoint and see what happens. If there is an error you will be able to see the error in real-time.
Limitations of Using Power Automate to Refresh Power BI Datasets
As with any great service, there are limitations to how much you can use Power Automate to refresh a Power BI dataset.
Many of the limitations are in place to prevent people from circumventing the number of times that a dataset can be refreshed.
Power BI Limits refreshes based on the Power BI license that’s being used.
- Pro License: Allows for 8 refreshes per day per dataset, with an interval of not less than 30 minutes between refreshes.
- Premium License: Offers up to 48 refreshes per day per dataset, with a 1-minute minimum interval.
In addition to Power BI limitations, Power Automate may limit the number of flows you can run based on the licensing tier that you are using.
Note: If you have files that update often in a SharePoint folder, these limitations may be difficult to overcome. Even while testing a Power Automate flow with several refreshes you can quickly use up the allowed 8 refreshes per day under a Power BI Pro License.
Additional Use Cases of Power BI and Power Automate
There are a number of additional features that you can use Power BI and Power Automate together. The following is a list of all of the actions available in Power Automate that can interact with Power BI.
- Add rows to a dataset
- Refresh a dataset
- Run a JSON Query against a dataset
- Run a query against a dataset
Goals and Check-Ins:
- Get goal check-ins
- Update a goal
- Update a check-in
- Create a Goal
- Create a Check-in
- Get Multiple Goals
- Get a Goal
- Add a note to a check-in
Reports and Exporting:
- Export to File for Power BI Reports
- Export to File for Paginated Reports
- Get scorecards
- Create a scorecard
Note: You’ll immediately notice that many of the actions are related to Power BI Goals, Check-ins, and Scorecards. On the surface it may not make sense, unless you are familiar with Power BI Goal Reporting. It’s a great feature that lets people take immediate action when KPIs run out of range.
Another cool use case for Power BI and Power Automate is embedding Power Automate flows within a Power BI report. It’s a fairly new feature that you can learn more about from the following video.
You can refresh Power BI reports with Power Automate by building a quick Power Automate Flow. Within a couple of steps, your Power BI dataset with be refreshing itself in no time!
To make building the workflow even faster, try Microsoft’s new Copilot AI assistant. Describe the process you want to automate and Copilot will create one for you.
Keep in mind the Power BI Pro and Premium licensing restrictions of 8 or 48 times in a 24 hour period.
Power Automate and Power BI are better together, and Microsoft is integrating a number of features into both platforms to help them work more efficiently together.