[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