Power Automate embedded in Power BI

Power Automate embedded in Power BI:

Introduction

Power BI is a great tool. And it is getting even better thanks to the effort made by Microsoft to integrate its softwares within each other. Today, we explore a new integration: the integration of Power Automate Flows into Power BI Reports !

Keep in mind that this is still a Preview functionality, but we hope it will get generally available soon.

Don't hesitate to check this Chronicle to have information about Power Automate. 

Quick Presentation

The embedding of Power Automate is done with a Power BI visual.

The visual appears as a button on the Power BI Report. This button has an "Edit" functionality that opens the Power Automate Portal, fully embedded in Power BI Desktop. You can create a Power Automate Flow from there.

When the button is clicked, we can choose data to pass from the report to the Power Automate Flow. The data is contextual: only data filtered in the report is used ! Then you can do anything you want with this data in the Flow. You will only be limited by the power of Power Automate... which is a very powerful tool ! 

Tutorial

Now let's discover this integration with a tutorial.

We use a very simplified use case, the main idea being to discover the tools.

We have a Power BI Report linked to the Opportunity data of a CRM (The data comes from a demo environment of Dynamics 365). I have created a small report on this Opportunity data.

And in this report, we have buttons (our Power Automate visuals) that allow to qualify or disqualify the selected Opportunities. We will focus on the "qualify" button to see how it is done.

Here is the appearance of the final report:

You can see various visuals showing data about opportunities. In the bottom-right corner of the report, there are the 2 button corresponding to the Power Automate visuals. These two buttons trigger the Flows.

To add the Power Automate visual in Power BI when you don't have it yet, you can click the three dots in the "Visualizations" pane, and then add the "Power Automate" visual:

Now, let's see how the buttons are implemented. 

In the visual Power Automate, you can choose the fields to be passed to the Power Automate Flow:

Here, we pass the fields "name", "estimatedvalue", "statuscode" and "opportunityid" of the Opportunity Table to the Flow.

By clicking the three dots on the button, and then "Edit", it opens the Power Automate Portal, fully integrated in Power BI! 

Below is the Flow used by the button:

So this is really the usual interface to create a Flow. Let's have a look at the steps in the Flow.

The trigger of the Flow isn't configurable. The trigger returns in output:

• The records filtered in the report
• With fields passed in the button (as seen previously)

Here is an example of the JSON in output:

{
    "headers": {
        "Accept": "*/*",
        "Accept-Encoding": "gzip,deflate",
        "Accept-Language": "en-US,en; q=0.9",
        "Expect": "100-continue",
        "Host": "prod-65.westeurope.logic.azure.com",
        "User-Agent": "Mozilla/5.0,(Windows NT 10.0; Win64; x64),AppleWebKit/537.36,(KHTML, like Gecko),Chrome/86.0.4240.198,Safari/537.36",
        "origin": "ms-pbi://pbi.microsoft.com",
        "sec-fetch-site": "cross-site",
        "sec-fetch-mode": "cors",
        "sec-fetch-dest": "empty",
        "X-MS-APIM-Referrer": "https://europe-002.azure-apim.net/apim/logicflows/81E86A9F3CC04EE9B5CD56F6D3C2E249-2BFE0D5665436769/triggers/manual/run?api-version=2016-06-01",
        "x-ms-client-region": "europe",
        "x-ms-flavor": "Production",
        "x-ms-gateway-object-id": "",
        "X-MS-APIM-Tokens": "*sanitized*",
        "X-MS-APIM-Referrer-Prefix": "https://europe-002.azure-apim.net/apim/logicflows/81E86A9F3CC04EE9B5CD56F6D3C2E249-2BFE0D5665436769",
        "X-MS-APIM-Callback": "https://europe-002.consent.azure-apim.net",
        "x-ms-user-id": "969423b1-5adf-4684-be0b-c006b83b89fd",
        "x-ms-user-name": "System Administrator",
        "x-ms-user-name-encoded": "U3lzdGVtIEFkbWluaXN0cmF0b3I=",
        "x-ms-user-email": "admin@CRM545215.onmicrosoft.com",
        "x-ms-user-email-encoded": "YWRtaW5AQ1JNNTQ1MjE1Lm9ubWljcm9zb2Z0LmNvbQ==",
        "x-ms-user-timestamp": "2021-06-21T06:06:34.1327471Z",
        "X-Forwarded-For": "92.184.110.172",
        "Content-Length": "536",
        "Content-Type": "application/json"
    },
    "body": {
        "entity": {
            "Power BI values": [
                {
                    "name": "10 orders of Product SKU JJ202 (sample)",
                    "estimatedvalue": 22000,
                    "statuscode": 1,
                    "opportunityid": "774C29E4-4BB0-EB11-8236-000D3AB6F6C0"
                },
                {
                    "name": "4G Enabled Tablets",
                    "estimatedvalue": 3257500,
                    "statuscode": 1,
                    "opportunityid": "BE0E0283-5BF2-E311-945F-6C3BE5A8DD64"
                },
                {
                    "name": "Audio",
                    "estimatedvalue": 747000,
                    "statuscode": 1,
                    "opportunityid": "BC0E0283-5BF2-E311-945F-6C3BE5A8DD64"
                },
                {
                    "name": "Audio Output",
                    "estimatedvalue": 0,
                    "statuscode": 1,
                    "opportunityid": "B80E0283-5BF2-E311-945F-6C3BE5A8DD64"
                }
            ]
        }
    }
}

Then, for each entity returned, we create a "Opportunity Close Activity" record. (This is how an Opportunity can be closed, not directly by updating the Opportunity). You can maintain the "Ctrl" key to click the Button in Power BI Desktop.

And that's basically all! In a very quick time, we are able to create actions in a Power BI report using Power Automate !

Here, we use a simplified use case in which we qualify opportunities. But the amount of use cases could be infinite, have fun!

Limitations

Here are 2 limitations that I could spot:

• The Power Automate Flow created with this method is created in the Default Environment of Dataverse. The environment can't be changed. This is probably because Power BI stands at the tenant level, and not at the environment level.
• I could not find the Dataverse connector in the Power Automate embedded. It could be due to the fact that the Flow is in the Default Environment. I guess the connector will be made available soon. So for the tutorial, I used the Dynamics 365 connector (which will be deprecated in the future).

This integration is still in Preview, but we hope that it will be Generally available soon.

Licenses

The licenses depend only on what you want to do with Power BI and Power Automate. There is no additional license required for using the Visual to integrate Power Automate into Power BI.

You can check the licences of Power Automate here. And the licenses of Power BI here.

Conclusion

This integration of Power Automate in Power BI allows to extend the functionalities of a Power BI report by creating actions from it. It is really simple to put in place, and it can bring great enhancement to a Power BI Report.

I recommend having a look at it and keeping it in mind when designing IT solutions !

Power Automate embedded in Power BI