Power Platform – Aggregate data using FetchXml | Blog

I’ve been finding that many developers I meet are not familiar with all of the powerful functionality available when querying Dataverse. One of the features is the ability to aggregate data when fetching it using FetchXml.

This can be particularly useful in situations when you want to reduce the logic needed inside your automation & offload the heavy lifting to tools you already have available.

Power automate & logic apps both have pricing models which encourage you to reduce the number of actions you make. I often see people query Dataverse, only to iterate over the dataset to calculate something you could just ask the API for up front.

Documentation

The documentation for the aggregation functions is quite good.

It can be found here

Available functions

FunctionReturn Value
avgThe average value of the column values with data.
countThe number of rows.
countcolumnThe number of rows with data in that column.
maxThe maximum value of the rows in that column.
minThe minimum value of the rows in that column.
sumThe total value of the column values with data.

Example data

I’m using the Microsoft example, this is the data I’ve created in my environment:

here

Example use

In my example I’m going to try and get the sum of account employees from my environment. The benefit of doing this using FetchXml is it results in only one action to do the calculation.

Had I done the List action and then an Apply to each this could result in thousands of requests to Dataverse, risking throttling & the need to purchase more requests.

here

Conclusion

Super Early Bird
The ability to aggregate data using FetchXml is a lesser known but very powerful feature of Dataverse, It makes me wonder, how many other features are available in the Organization Service or WebAPI that are also under utilized?

About the Author

Clive Oldridge

Power Platform Solution Architect

Reference:

Oldridge, C (2025). Power Platform – Aggregate data using FetchXml. Available at: Power Platform – Aggregate data using FetchXml | Blog [Accessed: 15th January 2025].

Share this on...

Rate this Post:

Share: