cancel
Showing results for 
Search instead for 
Did you mean: 
LauraGB

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 

Anonymous

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

Laura,
I just had a vendor suggest this very same approach this morning in a Teams conference call.
This seems redundant as the platform already has all this data. Do you know of any PowerAutomate API calls that could be used to surface this data?

@LauraGB  @annetoal Can you please share the trigger step. And how are you getting the flow name under Add rows to a dataset. step.
Please share the pics of each step. Thanks!

About the Author
  • 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.
  • I am a Microsoft Business Applications MVP and a Senior Manager at EY. I am a technology enthusiast and problem solver. I work/speak/blog/Vlog on Microsoft technology, including Office 365, Power Apps, Power Automate, SharePoint, and Teams Etc. I am helping global clients on Power Platform adoption and empowering them with Power Platform possibilities, capabilities, and easiness. I am a leader of the Houston Power Platform User Group and Power Automate community superuser. I love traveling , exploring new places, and meeting people from different cultures.
  • Read more about me and my achievements at: https://ganeshsanapblogs.wordpress.com/about MCT | SharePoint, Microsoft 365 and Power Platform Consultant | Contributor on SharePoint StackExchange, MSFT Techcommunity
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • Founder of SKILLFUL SARDINE, a company focused on productivity and the Power Platform. You can find me on LinkedIn: https://linkedin.com/in/manueltgomes and twitter http://twitter.com/manueltgomes. I also write at https://www.manueltgomes.com, so if you want some Power Automate, SharePoint or Power Apps content I'm your guy 🙂
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 15 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • Big fan of Power Platform technologies and implemented many solutions.
  • Passionate #Programmer #SharePoint #SPFx #M365 #Power Platform| Microsoft MVP | SharePoint StackOverflow, Github, PnP contributor
  • Web site – https://kamdaryash.wordpress.com Youtube channel - https://www.youtube.com/channel/UCM149rFkLNgerSvgDVeYTZQ/