Power Automate - Fast Data Aggregation - Group By, Sum, Count

In this video, I will show you how to group by, count and sum (add large arrays of numbers) values from a data source, such as SharePoint, using the most efficient method possible. I will use select to get distinct values, then convert the JSON data into XML. I will use select again to sum and count the values with XPath expressions. I will also demonstrate how to use xpather.com, a free online tool that helps you write and test XPath expressions. You will learn how to build a complete solution that can handle complex data aggregation scenarios in Power Automate Cloud Flow. No apply to each actions were harmed during the recording of this video. Only 7 actions and therefore 7 api calls are used in this flow to do all of the above.

[00:00:00] The purpose and benefits of data aggregation
Helps with writing, researching, and organizing data
Reduces the amount of API calls and avoids throttling
Enhances the performance and functionality of flows

[00:01:02] The example scenario and data source
A SharePoint list of employee expenses with 50 items
The goal is to get the total sum, the group by employee, and the sum and count per employee
A comparison with the built-in group by and sum features in SharePoint

[00:02:14] The steps of building the flow
Using a manual trigger and a get items action
Using a select action to get an array of employee IDs
Using a compose action with a union expression to get the distinct employee IDs
Using another compose action with an object to create a root for XML conversion

[00:05:02] The techniques of using XML and XPath
Using another compose action with an XML expression to convert the JSON into XML
Using another compose action with an XPath expression to sum up the total cost of all expenses
Using a select action with a concat expression to create dynamic XPath expressions for each employee
Using XPath expressions to get the name, email, total cost, and count of transactions for each employee

[00:08:14] The final output and verification
Using a compose action to display the final output as an array of objects
Comparing the output with the SharePoint list and the group by view
Explaining the advantages and limitations of this approach

[00:10:12] The setup and structure of the flow
The flow has a manual trigger and a get items action
The flow uses select, compose, and xpath actions to manipulate data
The flow outputs the total sum, the group by, and the individual sums and counts

[00:14:02] The use of select and compose to get distinct values
Select allows to select values from an array
Compose allows to create an object with a root and values
Union and sort expressions can be used to get distinct and sorted values

[00:17:15] The use of compose and xpath to convert JSON to XML
XML allows to use xpath expressions to query data
Compose allows to convert JSON to XML using the xml function
XML can be tested and validated using the Xpaather tool

[00:20:10] The use of xpath to perform data aggregation
Xpath allows to use expressions such as sum, count, and text
Xpath can be used to filter data by employee number
Xpath can be used to get the name, email, cost, and count for each employee

[00:24:22] The final output and review of the flow
The flow runs fast and returns the expected results
The flow uses only seven API calls and no apply to each
The flow can be modified and improved as needed

Check out my efficiency playlist for more great videos    • Power Automate Efficiency  

#PowerAutomate #Tutorial #demo

Please buy me a coffee https://www.buymeacoffee.com/DamoBird365 ☕