How to use Power BI to understand Power automate flow runs.

In this article we'll learn how to extract meaningful information from a power automate flow's history run.

Anyone who has ever used power automate knows the importance of run history, it gives the complete history of a power automate flow. BUT, sifting through that information isn't so easy for below reasons.

1. Only 50 history records are shown at any given time. However, we won't be solving that problem here, but will discuss the options in the next article.
2. The CSV structure is complex and difficult to understand in excel.

Download the flow CSV.

Open your flow and download the CSV as shown below.

Understanding the CSV

This information is simple and most important.

The CSV contains the following fixed columns.

• Run start time
• Run end time
• Run ID,
• Run status,
• Run error message
• Run link

In addition, for every action in your flow, the CSV generates 6 dynamic columns.

• Status
• Method
• Input
• Output
• start time
• end time

So if you've a declared a variable as shown below.

You'll see below columns in CSV.

Load the file in PBI

Download the file and start power BI for desktop. Then click the Get Data button and select Text/CSV.

Edit the CSV and see if it contains "SEP=,", If yes, remove it.

Now select the CSV and load it.

Editing the report.

From the left hand side select the "tabular view" and from right side choose the columns that you're interested in, remove all the columns that you don't need.

Extracting new columns with useful information.

Now that our report is ready, we'll reshape it with the information that we need and remove everything else. For example, our power automate runs on every contact's update, so we need contactID as a separate column.

In the "Data" pane on the right side, click on the ellipsis and click "edit query", optionally, you can right click on any column and select "edit query".

Parsing JSON and creating new columns.

Our trigger-output columns looks like this. We aren't interested in anything else other than the "body.text" which carries our "contactID".

{
  "headers": {
    "Accept-Language": "en-US",
    "User-Agent": "azure-logic-apps/1.0,(workflow b05fb42b40b044688263304c634d15e8; version 08584774480471587205),microsoft-flow/1.0",
    "x-ms-workflow-id": "b05fb42b40b044688263304c634d15e8",
    "x-ms-workflow-version": "08584774480471587205",
    "x-ms-workflow-name": "75f650e7-19f6-fcd9-8b20-b1476b0e8d3e",
    "x-ms-workflow-system-id": "/locations/EU/scaleunits/prod-209/workflows/b05fb42b40b044688263304c634d15e8",
    "x-ms-workflow-run-id": "08584774480226094772216",
    "x-ms-workflow-run-tracking-id": "6a39f3ec-7f2b-43c0-bc25-b6828e32ee45",
    "x-ms-workflow-operation-name": "Run_a_Child_Flow_for_each_Contact_Id_-_CUSTOM_INTEGRATION.Child.SendMemberUpdate",
    "x-ms-workflow-repeatitem-scope-name": "Foreach_Contact_Record",
    "x-ms-workflow-repeatitem-index": "48",
    "x-ms-workflow-repeatitem-batch-index": "0",
    "x-ms-execution-location": "westeurope",
    "x-ms-workflow-subscription-id": "eef517ff-955c-439b-b949-5cc79697d005",
    "x-ms-workflow-resourcegroup-name": "-B29054CC3BC5E88A87BC3EE3F3665757-ENV",
    "x-ms-tracking-id": "17fb88bd-b3b7-404b-9af9-62eb1585c438",
    "x-ms-correlation-id": "17fb88bd-b3b7-404b-9af9-62eb1585c438",
    "x-ms-client-request-id": "17fb88bd-b3b7-404b-9af9-62eb1585c438",
    "x-ms-client-tracking-id": "08584774483337758069123271052CU62",
    "x-ms-action-tracking-id": "4a3146c7-5f15-4691-ba4a-bf59cfd378dd",
    "x-ms-client-keywords": "resubmitFlow",
    "x-ms-activity-vector": "00.01.IN.0Z.IN.0L.IN.0E",
    "Content-Type": "application/json; charset=utf-8",
    "Content-Length": "47"
  },
  "body": {
    "text": "000a65ee-63a6-5a7e-d894-167d26f116e6"
  }
}        

Edit query

right click on the "Trigger output" column and select "edit query". This will launch the power query editor in a separate window.

Select "Trigger output", then on "Add columns" tab, select "parse JSON".

Now use the Power BI expander button to expand the JSON to the fields which we want as columns, in our case, we we just need the "body.text" property which gives us the contact ID.

And that's it, your new column is ready, now you can remove the "trigger output" column altogether. you can rename the new column to any text value from column tools.

So, we saw how we can create a report out of power automate run history, in next article we can see how we can query all the power automate runs as a virtual dataverse table.