Blogs

Group by in Array/Collections in Power Automate/Logic Apps

Inkey, December 1, 2023 8318 Views

Power Automate provides a versatile set of actions and expressions that allow you to craft logic to meet a wide range of requirements. You can utilize these capabilities for data processing, filtering, and sorting tasks, among others. The ‘Group By’ feature in Power Automate is a powerful tool that enables you to establish a new table where you can easily compute metrics such as Minimum, Maximum, Count, and Sum for any specific field within a collection or array. So lets see how we can use Group by in PowerApps and Logic Apps.

Lets take an example to understand this in more detail

In this scenario, we’re working with a collection that contains multiple fields. With our focus on learning how to implement ‘Group By’ functionality, we will specifically focus on the ‘Salary’ and ‘Gender’ attributes within the collection. Our goal is to create ‘Group By’ functionality based on the ‘Gender’ attribute. This ‘Group By’ operation will identify unique gender values and calculate the sum of the salaries for each respective gender.”

Now, we’ll walk through an illustrative example of how to fulfill the ‘Group By’ requirement using Power Automate.

Step 1: Here, we are working with a single Excel file that contains multiple records, each representing employee details.

Step 2: Let’s begin by creating a new Power Automate flow, and within this flow, we will use the ‘List Present Row in Excel’ action. In this action, you’ll have the opportunity to select a specific table from your Excel file, which you’ll subsequently use to implement the ‘Group By’ operation on your collection data.

Step 3: Add a new select action to get only Gender Column from the List Collection

The below Select action will get only Gender column in the output.

Step 4: Use compose action from data operation to union on Gender for Group by to get only unique values from a collection

Output:

Step 5: Initialize Array variable to Store Grouped Object

Step 6: Loop on the “union action” output and filter the collection to get the sum of Salaries per Gender.

Step 7: Next, in this ‘Select’ action, you need to specify only one field where you want to perform operations such as Sum, Max, and Min.

Step 8: Use a ‘Compose’ action step to create the root of ‘SalaryTotal’ after grouping by a specific gender.

Step 9: Use the ‘Append to array’ action to add an object containing ‘Gender’ and ‘SalaryTotal’ properties to an array. The ‘Gender’ value is obtained from the ‘Apply to Each’ loop and stored in the array. The ‘SalaryTotal’ property directly calculates the sum from the collection, as seen in the screenshot below. The resulting object is then appended to the ‘GroupByGenderArray’.

Output:

In this blog, we have covered a range of topics, including how to create Power Automate workflows with group-by functionality on collections or arrays. We explored various concepts such as filtering arrays, working with actions, using ‘Select,’ understanding XPath and XML, and utilizing the root of XML. This comprehensive guide has covered essential knowledge-based topics and demonstrated how to achieve the ‘Group By’ functionality in Power Automate and Logic Apps.

Hope this helps!

---

ATM Inspection PowerApp to ease ATM inspection and report generation process.
https://www.inkeysolutions.com/microsoft-power-platform/power-app/atm-inspection

Insert data into Many-to-Many relationship in Dynamics CRM very easily & quickly, using the Drag and drop listbox.
http://www.inkeysolutions.com/what-we-do/dynamicscrmaddons/drag-and-drop-listbox

Comply your Lead, Contact, and User entities of D365 CRM with GDPR compliance using the GDPR add-on.
https://www.inkeysolutions.com/microsoft-dynamics-365/dynamicscrmaddons/gdpr

Create a personal / system view in Dynamics CRM with all the fields on the form/s which you select for a particular entity using the View Creator.
http://www.inkeysolutions.com/what-we-do/dynamicscrmaddons/view-creator