Power BI and Power Automate
I consider myself a #PowerAddict, meaning I’m a nerd throughout the whole PowerPlatform and not just Power BI. You see, everything is connected! You can leverage PowerApps to get your input to datasets, measure it in Power BI to filter out valuable insights and act on these insights automatically using Power Automate as an example. In this blogpost I want to shine some light on the ways Power BI and Power Automate can interact and make your life easier! Let’s dig in!
These are the things covered in the post. Scroll to whatever section tickles your senses the most!
- Triggers
- Power Automate visual in Power BI (Power BI Button trigger)
- Goal triggers in Power Automate
- Data driven alert trigger in Power Automate
- Actions
- Run a Query against a Dataset
- Goals-actions
- Refresh Dataset
- Add rows to a dataset
- Run a
Triggers
The triggers in Power Automate is used to set of automated flows. It’s, as the name suggests, the trigger for the flow to start. There are 3 main areas I’ll cover in this post that can start a flow from Power BI.
Power Automate visual in Power BI (Power BI Button trigger)
This trigger is used on a button that you can embed into your Power BI canvas area. You create the flow to trigger manually and then you can select the Power BI button trigger from there. This is a convenient way to, for example, trigger a new refresh of the dataset when you cannot use DirectQuery. Let’s for example assume that you have your tasks in a SharePoint-list. You can use the Power BI report to quickly find tasks that needs updates and when you’re done, you can refresh the report to see how your changes reflect the bigger picture.
Inside Power BI Desktop, add the Power Automate button visual from the Visualizations pane, drag a field into the Values field and select your flow.
This is a great way to provide data to your flow to automate things such as sending notifications in teams and more!
Goal triggers in Power Automate
There are a number of Goal triggers in Power Automate! Goals is the old name for “Metrics” in Power BI, where you create scorecards and assign goals to your card. These goals can then be assigned to users as owners of the goal, there can be check-in made to the goals and the actual values measured for a goal might change. All these things can trigger Power Automate to start a flow!
You might for example trigger a flow on a new owner, check against a list of users if this new owner has ever been the owner of a goal in a Power BI Scorecard before and if not, add the person to that list and also automatically reach out with a Teams chat message and ask if a 15 minute introduction is needed for this new owner.
It could also be that the goal of having no more than 10 prio 1 incidents in the Service desk year to date has reached it’s threshold and this could automatically schedule a meeting 2 weeks from today with 5 selected people to discuss reactive mitigation methods.
Data driven alert trigger in Power Automate
Before metrics, there where the data driven alerts! I wouldn’t say this is used to a very large extent but never the less, it’s quite a good feature. It works like this:
- Go into your report and find a visual with only 1 value (a card or gauge)
- Pin this visual to a dashboard
- Go to the dashboard and on the three dots, select data driven alert
- Decide if your threshold is above or below a certain number and how often the alert should be triggered
- Now you can select this alert as the trigger of a Power Automate flow
As soon as you saved the alert it will appear in the drop down for the flow trigger.
Actions
Run a Query against a dataset
As this blog post is written, the action to run a query against a dataset is fairly new. It is however extremely useful! With this action, you can latterly make use of tables created in your reports as inputs into your flow! A table in a report could be filtered from multiple other tables, based on the relationship model and it could show values based on really complex measures!
Sounds simple enough, right? The issue is the query text. But you know what? There’s a trick for it!
Let’s imagine this table from the Center of Excellence Starter kit report. It shows display name of makers, displayname of enviroment, number of duplicate flows as well as number of created apps. It has multiple filters and show data from multiple tables in the model.
Here’s how you get the query text that you can paste into your flow!
Oh, and this is about half of the query. If you feel like writing out manually, be my guest! Otherwise, refer to the tip above.
Goals-actions
There are multiple actions that can be preformed towards Power BI Metrics using Power Automate. For example adding notes to a check-in, create new check-ins, create new goals, create a new scorecard, get scorecards and goals or update your goals.
So what can this be used for, huh? Well let’s for example say you run an organization with multiple departments doing similar things. It could be stores spread out across the land, each doing the same things but still separately. Using a flow that triggers by the touch of a button, you could create a Scorecard template, fill it with goals and assign owners automatically. It might for example be an initiative where each store is going to work towards certain sales, an improved culture or increased use of digital tools. Progress is measured in goals within a scorecard.
Refresh Dataset
Another really useful action is to refresh a Power BI Dataset. From your mobile phone you can’t access the datasets in the Power BI app for example and so if you want to trigger a refresh swiftly you could instead use a button triggered flow in Power Automate to refresh it for you. Let’s for example say you have a dataset in import mode but it only takes a few seconds to refresh the data. You’re standing at the coffee machine (you know, that old water hole experience we had before the pandemic) and you’re discussing something with a colleague when the urge to show the latest data in your report kicks in! Sure you could show data that’s potentially a few hours old, but why do that when you could just grab your phone, refresh the dataset and then show the refreshed report?
Another, perhaps more useful scenario, is if you have a form in forms. It’s filled in every few days or even weeks. The data is collected and visualized in a Power BI Report. If you schedule the refresh, someone might fill in the form just after the refresh was done and you’d have to wait for the results to show up. If you refresh daily, you’d refresh many, many times without any new data ever being added to the dataset. What if instead, as the last step of your flow collecting the data from your form, you simply refresh the dataset?
As a side note here, I would like to add that if it’s important that you have up to date data in your reports, consider using a data source that allows for DirectQuery.
All you need for your refresh is the workspace ID and the Dataset ID, both selectable from the drop downs.
Add rows to a dataset
Woa! What are we talking about here? Adding rows to a dataset? “Don’t you just mean adding rows to the source data?”
In this case, no. I’m actually talking about the Streaming dataset where you experience real time data. In this solution, instead of fetching the data from a source, you push the data into the dataset and at the same time as the row is added, it appears in the report as well.
If you know how to code, you can push the data that way. If you don’t (like me), you can let PowerAutomate do that job for you! Any record you have in a flow can be pushed to your dataset.
About The Author
