Power Automate visual within PowerBI

Hello all,

Recently in the April 2021 PowerBI release, we got the PowerAutomate visual. At first sight one can begin to wonder where this might be useful. In todays example I will showcase where I see this being very helpful. Let me lay down the scenario:

I have a PowerBI report that shows me the past due invoices along with amounts. I will like to quickly send an email to that customer and remind him of the past due invoices directly from my PowerBI report.

1. Add the PowerAutomate visual in PowerBI desktop. You can follow this youtube video from Curbal.com that goes through it step by step. https://youtu.be/BnTipbooeP0
2. Insert the visual into PowerBI and add the fields you want to use in PowerAutomate.

3.Create the PowerAutomate flow and use the body from the output to start the flow. What’s important here is the body of the PowerBI trigger here is a JSON representation of the data inside PowerBI. Once you see that is when the possibilities open up to a lot of scenarios.

Do not be intimated if you don’t understand JSON or don’t know where to obtain the schema. What you can do at this point is add “{}” in the schema field, save your flow and then run it. When you run it, the flow will fail but what you can do at this point is grab a sample of the JSON that PowerBI trigger will generate.

Copy and paste the contents from the failed flow and then paste them in the schema field

Failed flow schema

paste the JSON lines into the schema

This will automatically generate the JSON schema for you.

4. The next steps is to use the data from the parsed JSON to generate an HTML table

You can rename the columns how you desire and add the fields from your report as needed.

My next step is to add a basic border to my HTML table. I am not an HMTL expert but there are plenty of blogs that showcase how to work with HTML tables with flow

The next step I had to in order to obtain the email address from the JSON. If you remember the output of the PowerBI trigger is a tabular representation of the data in JSON format, which means the email address of the customer and other fields will repeat as many times as invoices open. I had to find a way to just return one of the email addresses.

I used this sample expression to get it

body('Parse_JSON')?['entity']?['Power BI values']?[0]?['Email']

This basically is obtaining the first (PowerAutomate is base 0) email from the JSON tuples.

For the body just insert the output of the compose action containing the HTML table

What’s so great about this process is that the process respects the filters being applied in the report. In my example above I filtered on just the customer I wanted to send the email to.

I supposed that one could also mass send reminders to all past due customers but that will be a more complex flow to build.

This it for today, a quick and dirty scenario solution. The point was to showcase where this new visual can come in handy.

Until next time.

Juan Sebastian Grijalba, CPA