Power Automate Flows management can be challenging, especially when dealing with a lot of flows and environments. The Power Automate Tracker simplifies this by monitoring Power Automate flows using Power BI.
Steps to Set Up Your Power Automate Tracker
1. Register Your App in Azure Portal Start by registering an application in the Azure portal (https://portal.azure.com).
2. Grant API Permissions After registering the app, grant the appropriate API permissions for accessing Power Automate data:
Power Automate - Delegated permissions
User
Activity.Read.All
Flows.Read.All
3. Generate a Client Secret Create and securely store a Client Secret. This will be needed to authenticate your API calls.
4. Get an Access Token in Power BI Use the Tenant ID and Client ID from the Azure portal to fetch an access token within Power BI.
Power Query function: GET Access Token Power Automate
() =>
let
clientId = "XXXXXXXX", //Id of your application from step 1
clientSecret = "XXXXXXXXX", //Client secret frm your application
username = "XXXXXXXX", //e-mail of your M365 account
password = "XXXXXXXXXX", //password of your M365 account
tenantId = "XXXXXXXXXX", //Id of your tenant
body = "grant_type=password&resource=https://service.flow.microsoft.com&
&client_id="&clientId&
"&client_secret="&clientSecret&"
&username="&username&"
&password="&password,
Data = Json.Document(
Web.Contents(
"https://login.microsoftonline.com/",
[
RelativePath = tenantId &"/oauth2/token",
Headers = [
#"Content-type" = "application/x-www-form-urlencoded"
],
Content = Text.ToBinary(body)
]
)
),
access_token = Data[access_token]
in
access_token
5.Retrieve Flows Data Use the environmentId to retrieve a list of your flows programmatically.
Power Query function: GET Flows
() =>
let
environmentId = "XXXXXXXX", //your Power Automate environmentId
Source = Json.Document(
Web.Contents(
"https://api.flow.microsoft.com/providers/Microsoft.ProcessSimple/environments/",
[
RelativePath = environmentId & "/flows?api-version=2016-11-01",
Headers = [
Authorization = "Bearer " & #"GET Access Token Power Automate"()
]
]
)
),
value = Source[value]
in
value
6.Retrieve Flow Run Details Fetch detailed flow run data using the environmentId. This helps in monitoring success rates and identifying failed runs.
Power Query function: GET Flow runs
(flowId as text) =>
let
environmentId = "XXXXXXXXXX", //your Power Automate EnvironmentId
Source = Json.Document(
Web.Contents(
"https://api.flow.microsoft.com/providers/Microsoft.ProcessSimple/environments/" & environmentId,
[
RelativePath = "/flows/" & flowId & "/runs?api-version=2016-11-01",
Headers = [
Authorization = "Bearer " & #"GET Access Token Power Automate"()
]
]
)
),
value = Source[value]
in
value
7.Create Tables in Power Query
Power Query code: Flows
let
Source = #"GET Flows"(),
#"ToTable" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"OpenColumn" = Table.ExpandRecordColumn(#"ToTable", "Column1", {"name", "properties"}, {"IdFlow", "properties"}),
#"OpenProperties" = Table.ExpandRecordColumn(#"OpenColumn", "properties", {"displayName", "state", "createdTime", "lastModifiedTime", "flowFailureAlertSubscribed", "isManaged"}, {"FlowName", "State", "CreatedTime", "LastModifiedTime", "FailureSubscribe", "Managed"})
in
#"OpenProperties"Power Query code: Flow runs
let
Source = #"GET Flows"(),
#"ToTable" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"OpenColumn" = Table.ExpandRecordColumn(#"ToTable", "Column1", {"name"}, {"IdFlow"}),
#"CallCustomFunction" = Table.AddColumn(#"OpenColumn", "GET Flow runs", each #"GET Flow runs"([IdFlow])),
#"OpenFlowRuns" = Table.ExpandListColumn(#"CallCustomFunction", "GET Flow runs"),
#"GetFlowRuns" = Table.ExpandRecordColumn(#"OpenFlowRuns", "GET Flow runs", {"name", "properties"}, {"IdFlowRun", "properties"}),
#"OpenProperties" = Table.ExpandRecordColumn(#"GetFlowRuns", "properties", {"startTime", "endTime", "status", "code", "error"}, {"StartTime", "EndTime", "Status", "Code", "Error"}),
#"OpenError" = Table.ExpandRecordColumn(#"OpenProperties", "Error", { "message"}, {"ErrorMessage"}),
#"FilterRows" = Table.SelectRows(#"OpenError", each ([IdFlowRun] <> null)),
#"StartDate" = Table.AddColumn(#"FilterRows", "StartDate", each Date.From(Text.Start([StartTime], 10)), type date),
EndDate = Table.AddColumn(#"StartDate", "EndDate", each Date.From(Text.Start([EndTime], 10)), type date),
DurationMin = Table.AddColumn(EndDate, "DurationMin", each Duration.TotalSeconds(DateTime.FromText([EndTime]) - DateTime.FromText([StartTime]))/60, type number),
RemoveColumns = Table.SelectColumns(DurationMin,{"IdFlow", "IdFlowRun", "Status", "Code", "ErrorMessage", "StartDate", "EndDate", "DurationMin"})
in
RemoveColumns
Visualize Data in Power BI
Once you have the data, import it into Power BI and create the following tables:
Flows: Overview of all registered flows.
Flow Runs: Detailed run data for each flow, including success and failure rates.
Final Power BI Tracker
The result is a powerful Power BI dashboard giving you a complete overview of your automation landscape. This tracker helps you:
Identify failed runs quickly.
Monitor performance across multiple environments.
Improve automation governance and compliance.
Get started with Power Automate Tracker today and take control of your automation insights!
