crmtipoftheday: Tip #1451: Aggregates in Power Automate

[Blog bot]

Источник: https://crmtipoftheday.com/1451/aggr...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.

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).

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.




Second action takes that CSV and converts it to JSON:

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

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.

The post Tip #1451: Aggregates in Power Automate appeared first on Power Platform & Dynamics CRM Tip Of The Day.



Источник: https://crmtipoftheday.com/1451/aggr...power-automate