[Automation] Retrieve Power Automate Flow Run Logs and Visualise in Power BI

Level of Difficulty: Intermediate – Senior.

This post will focus on pulling the Power Automate flow run logs, using PowerShell, exporting them to a CSV file and visualising them in Power BI

What are the steps?

The steps that we will be following are:

  1. Setup PowerShell
  2. Get the data
  3. Visualise in Power BI

Setup PowerShell

You’ll need to install the PowerApps PowerShell modules which you could do by running the following commands as Administrator in PowerShell:

Install-Module -Name Microsoft.PowerApps.Administration.PowerShell
Install-Module -Name Microsoft.PowerApps.PowerShell -AllowClobber

Next, you’ll need to authenticate by running the Add-PowerAppsAccount command in PowerShell:

Add-PowerAppsAccount

If you’d like to execute the command without the prompt, you can use the following instead:

$pass = ConvertTo-SecureString "password" -AsPlainText -Force
Add-PowerAppsAccount -Username foo@bar.com -Password $pass

Get the Data

In order to find a flow ID (which you’ll need to export the logs, using the next query), you’ll need to dissect the URL. Navigate to the desired flow on the web version of Power Automate and retrieve the URL. This is what you’d like to navigate to, to get the URL break down below:

emea.flow.microsoft.com/manage/environments/Default-<EnvironmentID>/flows/<FlowID>/details?backUrl=%2Fflows%2F<FlowID>%2Fruns

From the above, you would like to copy the portion that would depict the Flow ID of the flow that you are looking at. Then, execute the following command in PowerShell, replacing <FlowID> with the ID you’ve retrieved:

Get-FlowRun <FlowID> | Export-Csv -Path '.\FlowExport.csv'

Visualise Data in Power BI

Open Power BI Desktop > Get Data > txt or csv. Then select the FlowExport.csv that was just generated from PowerShell. Then hit Transform Data and apply the following steps:

  1. Remove the top row (which contains TYPE System.Management.Automation.PSCustomObject)
  2. Use First Row as Headers
  3. Change StartTime column to Date/Time
  4. Split Internal by semi-colon, for each occurrence of the delimiter
  5. Remove all Internal columns EXCEPT Internal.2 (which should start with ‘id=/’)
  6. Rename Internal.2 to Internal
  7. Replace ‘id=/providers/Microsoft.ProcessSimple/‘ with ‘https://emea.flow.microsoft.com/manage/’ in Internal
    • This is so that more detail can be obtained on failed runs by navigating to the URL from the report

The dataset should look similar to this before hitting Close & Apply:

Now you can visualise your logs on Power BI, like by adding a Clustered Column Chart visualisation to the page:

Need help? Feel free to drop a comment below or reach out to me via email, jacqui.jm77@gmail.com

Published by Jacqui Muller

I am an automation architect by current profession who enjoys dabbling in software development, RPA, IOT, advanced analytics, data engineering and business intelligence. I am aspiring to complete a Master's degree in Computer Science within the next two years. My competencies include a high level of computer literacy as well as programming in various languages. I am passionate about my field of study and occupation as I believe it has the ability and potential to impact lives - both drastically and positively. I come packaged with an ambition to succeed and make the world a better place.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: