Tip #1451: Aggregates in Power Automate

There is no easy way to calculate aggregates in Power Automate (except count, of course, which technically, is an aggregate). You do have a few options available to you: loop over the array and apply hideous formulas forgotten since the statistics/SQL course, use Graph API and bring magic of Excel formulas, use third-party connectors (which is not a bad option if you already paying for them). If your data coming from Dataverse you can use FetchXml. SharePoint data can be wrestled with CAML aggregations (yes, it’s a real thing). You can probably even cook some joujou with xml transforms.

Enter Code-Only Connectors (CoC). For a walkthrough watch Hour 16 “Mastering Custom Code in Power Platform Connectors” at Power Platform 24 Conference (double speed recommended, my speech patterns can be painfully slow after excessive travel). Here’s some magic we can do with just code by using Aggregate and CSV To JSON CoCs.

Aggregate array of numbers

Input is an array of primitive values that can presumably be converted to numbers.

Screenshot of an Aggregate action configuration. Values property is set to a formula creating an array of random numbers. Property called "Property" is blank indicating that the input is an array of primitive values.

Output is a set of Excel-like aggregate functions: Sum, Avg, StDev, Median, Min, Max, Product and a bonus ModeSngl (the most frequent value in the sequence).

Runtime output of the Aggregate action. Standard aggregate values like Sum, Avg, Min, Max, etc are included as output properties.

Wait, there is more!

Aggregate array of objects

When you have an array of objects and need some aggregates on a property, it’s an extra effort to extract the array holding just those numeric values. Our connector can take an array of objects, all you need to do is to tell it which property to aggregate.

This example uses sample CSV data containing some contact information including the age. The first action is a simple CSV set.

The image is a screenshot depicting a segment of a Microsoft Power Automate flow. It consists of three sequential actions aligned vertically. The first action at the top is titled "CSV Input" and is marked by a purple icon resembling a file or document. The second action is titled "Csv To Json" and is represented by an orange icon with a gear, indicating a transformation or processing step. The third and final action displayed is titled "Aggregate on Array of Objects" accompanied by a smiley face emoji, suggesting an operation that handles a collection of data structures.
Screenshot of CSV sample data

Second action takes that CSV and converts it to JSON:

Screenshot of a runtime output of the CSV to JSON action. Output contains a standard JSON object with properties corresponding to the input CSV columns

And the last step brings it home by taking the JSON object ‘as is’ and calculating aggregates on the ‘Age’ property.

The image is a screenshot of a Power Automate action titled "Aggregate on Array of Objects 😎". The input fields are Values and Property. Values is set to the output of the previous step which is a JSON object. Property set to the text "Age" indicating that this property should be used to calculate the aggregates.
The image is a screenshot of an Power Automate action output titled "Aggregate on Array of Objects 😎". It displays a JSON response structure with various fields. The "statusCode" is 200, indicating success, and there's a "headers" object containing HTTP headers such as "Transfer-Encoding", "Vary", "Request-Context", and "Date". Inside the "body" object, there are key-value pairs for aggregation results: "Sum" is 367, "Avg" (average) is 36.7, "Max" (maximum) is 52, "Min" (minimum) is 27, "StdDev" (standard deviation) is approximately 8.12, "Variance" is approximately 66.01, "Median" is 36.5, "ModeSngl" (single mode) is 29, and "Product" is a very large number, displayed in scientific notation as 3479971800000000.

There are plenty of plans to add other Code Only Connectors (CoCs). There are some decisions to be made: how to compose a single connector with all methods included, how to validate syntax, is it possible to create a test harness, shall we publish these as independent custom connectors or just leave it like that?

Want to get involved? Drop email to jar@crmtipoftheday.com and/or go straight to the source https://github.com/georged/code-connectors.