Using Group By and Aggregation in PowerApps

The GroupBy function returns a table with records grouped together based on the values in one or more columns.

Let’s explore and see how to do aggregation of values for a collection within GroupBy Function.

Let’s Assume you have the below data :

{dept:”Department1",prod:”pen”,qty:10,person:”Person1"},{dept:”Department2",prod:”pen”,qty:1,person:”Person2"},{dept:”Department1",prod:”pen”,qty:10,person:”Person3"},{dept:”Department1",prod:”pen”,qty:10,person:”Person4"}

Sample Data

Here, let’s assume our goal to aggregate the values by Product and Department.

Sample output

Directly display the aggregated values

You could use the below formula — in the Gallery.Items Property

AddColumns(GroupBy(_prodCollection, “prod”,”dept”, “subgroup” ), “Sum”, Sum(subgroup,qty) )

Here the _prodCollection = your data source.

To Generate the sample data

ClearCollect(_prodCollection,{dept:”Department1",prod:”pen”,qty:10,person:”Person1"},{dept:”Department2",prod:”pen”,qty:1,person:”Person2"},{dept:”Department1",prod:”pen”,qty:10,person:”Person3"},{dept:”Department1",prod:”pen”,qty:10,person:”Person4"})

Now to get the aggregation results in another gallery. You set the property of the “items” as

AddColumns(GroupBy(_prodCollection, “prod”,”dept”, “subgroup” ), “Sum”, Sum(subgroup,qty) )

Explanation :

GroupBy(_prodCollection, “prod”,”dept”, “subgroup” ) => First groups the data by “prod”,”debt” and stores grouped values in the subgroup column

Grouped Values

Now we add a column “Sum”, which will be resulting sum of all values under “qty” within a “subgroup” for each prod,dept combination.

If you want to store in the parsed value in the collections :

ClearCollect(_tempprodCollection, GroupBy(_prodCollection, “prod”,”dept”, “subgroup” ) );

ClearCollect(_finalcollection, AddColumns(GroupBy(_prodCollection, “prod”,”dept”, “subgroup” ), “Sum”, Sum(subgroup,qty) ) );