FetchXML Aggregation in a Flow using CDS (Current Environment) connector | Power Automate

Getting the count of records or averaging is one of the most commonly used Aggregate Functions in programming. FetchXML queries too facilitate aggregation. So here’s how you can utilize the same in a Flow using Common Data Service (Current Environment) connector [Because I’m waiting for it to be renamed to Dataverse Connector yet 😊]

To know about Fetch XML aggregation, here’s the Microsoft Docs link for the same – https://docs.microsoft.com/en-gb/powerapps/developer/data-platform/use-fetchxml-aggregation?WT.mc_id=DX-MVP-5003911

Scenario

Let’s assume, you want to get the count of records from a Common Data Service (Current Environment) connector. Here’s how you can do the same using existing Aggregate Functions provided by Fetch XML.

Disclaimer: This is of course, not he only way to get get the aggregate functions, you can implement custom logic after you’ve retrieved all the data as well.

  1. Here’s the Fetch XML I’ll be using to retrieve all the Accounts from the Dataverse environment.
    I’m using List Rows action from the connector which is this


    And this is the query which I generated from the Advanced Find in D365 CE


  2. Below are the changed I must make to work with Aggregates in Fetch XML.
    in the <fetch>, I’ll set aggregate=”true”
    And the columns which I’m using the Aggregate function on, I’ll mention the aggregate=”[Aggregate]” alias=”[AliasName]”

  3. Now, this query along won’t run and you’ll get the below error –
    An attribute can not be requested when an aggregate operation has been specified and its neither groupby not aggregate. NodeXml: [FirstAttributeInQuery]

  4. The reason being, since you are using Aggregate, only the columns on which aggregates are applied must exist. Hence, you’ll need to remove the other attributes which don’t have aggregate applied to them.
    |
  5. And the workable FetchXML will now look like this

  6. When you run this, these are the results you’ll get in which you’ll have the aggregate value.

  7. Observe the same below

Parse JSON to read the aggregate

Now, since you’ve got the aggregated results. You can do an extra step to read the value. There are several ways to contain this, but here’s a quick example of how I did it –

  1. Declare a variable. It must be outside of a For Each at all times.

  2. And in the For Each, because I’m selecting the Array inside the value attribute in the Fetch XML results, I can then use the sample data to generate the schema and use it. The loop will anyway run only once.

  3. And I’ll set the variable below

  4. And here’s the final result once you run it. Your scenario of usage may vary.

Hope this was useful!

Here’s a YouTube video I made to summarize this example –

Here are some more Power Automate / Flow posts you might want to check –

  1. Invalid type. Expected Integer but got Number error in Parse JSON – Error at runtime after generating Schema | Power Automate
  2. Secure Input/Output in Power Automate Run History
  3. Validate JSON Schema for HTTP Request trigger in a Flow and send Response | Power Automate
  4. Parsing Outputs of a List Rows action using Parse JSON in a Flow | Common Data Service (CE) connector
  5. Trigger Conditions not working in a Cloud Flow? Here’s Why | Power Automate Quick Tip
  6. Read OptionSet Labels from CDS/Dataverse Triggers or Action Steps in a Flow | Power Automate
  7. Asynchronous HTTP Response from a Flow | Power Automate
  8. FormatDateTime function in a Flow | Power Automate
  9. Tag a User in a Microsoft Teams post made using Power Automate
  10. Converting JSON to XML and XML to JSON in a Flow | Power Automate
  11. Office 365 Outlook connector in Cloud Flows showing Invalid Connection error | Power Automate
  12. Create a Team, add Members in Microsoft Teams upon Project and Team Members creation in PSA / Project Operations | Power Automate

Thank you!!

Aggregate functions in a Canvas Power App | Using on SharePoint Lists

Aggregate functions have one of the most common applications while building your Canvas Power App.

In this simple example, we’ll try to make common Aggregate Functions like Max, Min, Sum & Average work.

There are 2 more advanced Aggregate Functions, StdevP for Standard Deviation and VarP for Variance. (It might not make full sense in this post, but I added them anyway to see how we can apply for formula 😊 I’m sure your use-case will make more sense!)

SharePoint List

Let’s say, my SharePoint list looks like this. Scores of some people and their ranks

And I’m using Data Table to populate this into my Canvas Power App

Formulas

Here’s how I used for Formulas to use the Aggregate functions on the SharePoint list data I populated in my Canvas Power App

For all the Formulas above, I’ve written them inside a Concatenate function just so I could add some Text and then use the formulas. You may use differently.

In my case, the Formula goes like this –
Function(SharePointListName, ColumnName)

“Max” – Maximum Number in Score column of Scoreboard list


“Min” – Smallest Number in Score column of Scoreboard list

“Sum” = Total of all values in Score column of Scoreboard list

“Average” – Average of all values in Score column of Scoreboard list

“StdevP” – Standard Deviation arguments passed. Score column in this case

“VarP”-Variance of the arguments passed. Score column in this case

And that’s it! I tried to simply the explanation. However’s there much more to consider as well. Here’s the full documentation by Microsoft – https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-aggregates

You might want to check some posts on Canvas Power Apps as well –

  1. Count of total CDS records returned in a Canvas Power App connection [Quick Tip]
  2. Dependent OptionSets in a Canvas Power App for 1:N related CDS entities | Power Platform
  3. Restore older version of a Canvas Power App | Power Platform
  4. Implement character length validation in a Canvas Power App | Power Platform
  5. Logged In User details in a Canvas Power App
  6. Implementing Exit app, Logout and Confirm Exit features in a Canvas Power App
  7. Variables in Canvas Power Apps | Global and Context
  8. Get N:N records in a Canvas Power App using Common Data Service connector | Power Platform
  9. Sending Image from Canvas PowerApps to SharePoint Document Library using Flows
  10. Correctly connect to an Excel file in a Canvas PowerApp

Hope this was helpful!