cancel
Showing results for 
Search instead for 
Did you mean: 

Track Flows using Power BI

Introduction

This post is in three parts. The first part is to create the dataset in Power BI, the second part is to add actions to the flow and the final part is to create a simple report in Power BI.

 

Creating a Hybrid Dataset

For the first part we need to start in Power BI, which can be found at https://powerbi.microsoft.com. Power BI uses workspaces to organise datasets, reports and dashboards, with a free license you will only have access to My Workspace, that will work for this exercise.

  1. Click on My Workspace and then Datasets.
  2. In the top right hand corner, click +Create and select Streaming Dataset.
  3. Choose API as the source of the dataset and click Next.

    trackflow01.png

  4. Enter in a name for the dataset.
  5. Enter in the value names and data types.
  6. Turn on Historic data analysis to make the dataset a Hybrid of a streaming and push dataset. This means the data will be kept rather than only a temporary store.
  7. Click Create and the pane will show a message that the dataset has been created.
  8. Click close to show the dataset in the list.

    trackflow02.png

     

Add Actions to Flows

For every flow you want to track you’ll need to add two parallel actions, one for a successful run and one for a failed run. This will allow you to track not only how many flows ran but their final result.

  1. Edit the flow and click to add an action at the end of the flow and search for Power BI and select Add rows to dataset.

    trackflow03.png

     

  2. Rename the step to Add Success Row to dataset.
  3. Select the workspace, dataset and the table is RealTimeData.
  4. Enter in the values for the dataset. To make reporting easy we are going to split the when the flow happened into date and hour. The expression formula for the date and hour are:

    Date   substring(utcNow(),0,10)
    Hour  int(substring(utcNow(),11,2))
    .
    Enter Success for the response


    trackflow04.png

     

  5. Add a parallel branch to the previous step and create a step identical to the previous step with a response of Failed.

  6. This step needs to run if the flow is not successful.
    Click on the ellipse in the top right of the action and select Configure run after.
    Untick is successful and tick has failed, is skipped and has timed out.
    Click done to save the changes. The arrow into the action will change to red.
    trackflow05.png

     

  7. Save your flow. Repeat the above steps on the other flows you want to track.

Creating a Report

In this post we will create a simple report to show the flow activity.

  1. Back in Power BI and the dataset list and click on Create Report.


    trackflow06.png


     

  2. In the Visualizations pane select Stacked column chart.
  3. From the fields list, drag Hour to Axis, Response to Legend and Flow to Value.

    trackflow07.png

     

  4. A column chart will show the number of flows per hour. You could plot different charts using days, different flows etc
    trackflow08.png

     

Conclusion

This method could be expanded to track multiple flows and could track different aspects of the flow including times it took approvals to happen or how often approvals are requested.

Let me know how you used this idea.

Comments
Anonymous

Hi Laura, Great article!  One question, can you provide more detail on how to set up the actual Flow itself, like the Trigger and if the Flow is company wide or only my Flows? Thanks!

Hi mgilbert
These steps can be added to any flow. If you want to run a dummy test create a flow with a button trigger. As for if the flow is company wide, that depends on the trigger and how you share it. For example if the trigger was a document being updated in a library, anyone who had edit access to the library could trigger it and then your report would show how many edits were done in that library.

This is awesome, I was looking for Flow Analytics, etc to monitor my Flows and finaly I got this post.

 

Very well explained.

 

Thank you 

Hi @LauraGB

I am new to reporting on Power Automate and found your article. Stunning. This article only shows reports re Running and Failed or Skipped. What our guys need to know is where the flow is now. So that they could indicate the time required to complete the run.

Here in South Africa this is quite important because of power outages which upsets the flows and timelines.

Thank you

Niek

Hi @LauraGB,

 

Thank you for the article. I copied your steps and was able to produce a report in PowerBI that showed how many Flows succeeded each hour of the day. I would like to report on who was the Flow submitter and who was the approver, the result of the Flow (approved, rejected, or neither), by date. What variable names do I use to push that data from Flow into the PowerBI table?

 

Thanks,

Anne

Meet Our Blog Authors
  • Experienced Consultant with a demonstrated history of working in the information technology and services industry. Skilled in Office 365, Azure, SharePoint Online, PowerShell, Nintex, K2, SharePoint Designer workflow automation, PowerApps, Microsoft Flow, PowerShell, Active Directory, Operating Systems, Networking, and JavaScript. Strong consulting professional with a Bachelor of Engineering (B.E.) focused in Information Technology from Mumbai University.
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • Cambridge UK Power Platform User Group Leader, Technical evangelist and speaker. Always says yes to coffee! #LetsGetCoffee
  • Passionate #Programmer #SharePoint #SPFx #Office365 #MSFlow | C-sharpCorner MVP | SharePoint StackOverflow, Github, PnP contributor
  • I am building business processes and applications that are easy for users' to stick to, so they can follow and understand them. In overall I transform processes to be more reliable and effortless. I am a proud co-organizer of SharePoint Saturday Warsaw and active community member, blogger and international speaker.