The Power Automate Visual

One of the hardest problems in school data is how to connect analysis to action; you can have the most sophisticated BI report in the land but if its users do no more than look at it than you'd be better off without it. Of course, there isn’t an obvious straight line between data and action – we need professionals in the middle – but nevertheless insights about the reality in schools should always be designed to work in tandem with systems for changing that reality

For that reason the Power Automate visual is the most exciting addition to Power BI for ages. What it does is allow the user to initiate a ‘Flow’ of actions involving a selection of records in Power BI. The actions include creating a report, sending an email and updating a sharepoint list although there are many more. A way to think about it as the mirror image of power query – PQ applies a series of customisable steps to get data in to BI in the right way, Power Automate (PA) does the same but for getting data out

Setting up Power Automate

Firstly, you need to make sure you’ve installed Power BI Desktop version April 2021 or later. Next you need to go to the app market place and install Power Automate as a custom visual ( it doesn’t come as standard yet)

When you add the visual you see these steps. You add fields to the field well that you want to use in the flow (you can add visual level filters as well and the fields will all be affected by the filter context as with any other visual)

You then click Edit in the top right menu to set up the flow and apply it to the button and share it with other users

Finally, you can format the button in the usual way. As with regular buttons if you want to test it in desktop you ctrl+click

Test Environment

To demonstrate the visual I’ve set up a really simple dashboard that has a parameter (attendance between), a table and a power automate visual with the same fields. I created a measure called _attendance_ that has 2 salient features – it only shows attendance between the selected parameter (this isn’t crucial for PA it’s just so I can test with small numbers of records) and it uses FORMAT to format the attendance measure as text. This is critical for PA because native BI formatting does not propagate through the visual, so if you use your regular attendance measure it will give attendance as something like .95423523523

FORMAT([Attendance],"#.#%") will output attendance percentage to 1 dp

Use case 1: emailing contacts of a student

Setting up a flow to email the contacts of a student with their attendance is really easy. Click edit at the top right of the power automate visual. You’ll see a list of flows you’ve already created (they exist independently of which BI report they’re in) and you have the option of creating new ones – go for Instant cloud flow

The first step (power BI button clicked) is already there. Click ‘New Step’ and search for send email. If you have an Outlook account then you can use it so the emails come from you. I’m too cheap for that so I’m using the native Mail app’s ‘Send an email notification (V3)’

The visuals fields are available in the ‘Dynamic Content’ pop up. As soon as you place a value field (like ContactEmail) in the To property of the send email task then PA will automatically add an ‘Apply to each’ loop that will cycle through the records and send each one their own email – cool!

Use the other fields and text to write the email, just like a standard mail merge. Then save and apply

Note that in order for consumers of the report to be able to trigger the flow they need to have it shared with them. You can do this within the PA visual or at flow.microsoft.com

This is super useful as it means you don’t have to let everyone who can view the report trigger actions. Even so I think there’s a danger in using this as a direct communication tool with parents/staff of the number of messages escalating into spam territory

Use case 2: sending a set of records

To avoid the spam issue, a potentially more viable use case is sending a set of records as a table in an email. This involves a slightly more complicated flow

First add an Initialize variable step – this will turn our data table into an Array – the variable type is Array and for the value add the Power BI data item from the Dynamic Content selector

Next we create an HTML table from that array. Search for Create HTML table then from Dynamic Content place the variable from the previous step in the From field

Finally compose the email using the Output of the previous step in the email body

Use case 3: storing records in a sharepoint list

Say you want to record students for an intervention or keep track of incidents involving sexual assault but only after reviewing them first in BI. (You could complement this process by having a multi select slicer of individual students so that after filtering to say 10 or 20 via general conditions you could then chose/exempt individuals due to special circumstances)

Setting up the flow is dead easy, choose Sharepoint – Create item as the task, chose the list you want to update and match the fields. Once again BI will automatically create the for loop to cycle through and add each record

In the screenshot below you can't tell that each item is a different field but it's clear in the Dynamic Content box where you drag them from.