Power Apps Collection GroupBy [How to Use]

In this Power Apps tutorial, I will explore everything related to Power Apps Collection GroupBy. Then, we will see how to create a collection using multiple records and how to group multiple records into a single record in a Power Apps collection.

Additionally, we will discuss how to filter group data in the Power Apps collection and how to ungroup the group record from a Power Apps collection. Lastly, we will cover how to get aggregate results from a Power Apps collection group.

Power Apps Collection GroupBy()

We can use the Power Apps GroupBy() function to return a table with records grouped based on the values in one or more columns.

Also, we can group all the records in a single record, which will act as a nested table in the Power Apps collection.

Power Apps GroupBy() Syntax

GroupBy(Table, ColumnName1[ColumnName2, ... ], GroupColumnName)

Where,

1. Table = It is required, and it will be grouped.
2. ColumnName (s) = We can create a group based on the column names.
3. GroupColumnName = It stores the group records like a nested table.

• For example, I have two Power Apps collections, i.e., colTravel [1st image] and colDestination [2nd image].
• The first collection [ colTravel ] has the below headers:

• There is a second collection named “colDestination” and inside this, I will create a nested table [GroupDestination] by grouping the two columns from the previous collection, such as Airline and Destination.

• Once the nested table [GroupDestination] is clicked, it will display the grouped records as shown below.

To achieve the above example, follow the below-mentioned steps. Such as:

• Open Power Apps with your credentials and create the Power Apps Canvas app where you want to group the collection records.
• Then, select the App (from the left navigation) and choose the OnStart property to create the collections like below.

OnStart = ClearCollect(
    colTravel,
    {
        TripTitle: "Company anniversary trip",
        Destination: "Indiana,UK",
        TravelStartDate: "9/25/2023",
        TravelEndDate: "9/31/2023",
        Airline: "Alaska Air",
        EstimatedAirfare: 6000,
        Hotel: "Indiana Hotel",
        EstimatedHotelCost: 1500,
        Requestor: "Lidia Holloway",
        Approved: "Yes"
    },
    {
        TripTitle: "Research interviews",
        Destination: "Bengaluru,India",
        TravelStartDate: "10/15/2023",
        TravelEndDate: "10/20/2023",
        Airline: "SouthWest",
        EstimatedAirfare: 800,
        Hotel: "Hotel Royal Orchid Bangalore",
        EstimatedHotelCost: 1200,
        Requestor: "Lynne Robbins",
        Approved: "No"
    },
    {
        TripTitle: "Design sprint",
        Destination: "New York,UK",
        TravelStartDate: "11/22/2023",
        TravelEndDate: "11/28/2023",
        Airline: "British Airways",
        EstimatedAirfare: 5500,
        Hotel: "Hotel Mela Times Square",
        EstimatedHotelCost: 1800,
        Requestor: "Joni Sherman",
        Approved: "Yes"
    },
    {
        TripTitle: "Sales team conference",
        Destination: "Georgia,UK",
        TravelStartDate: "12/20/2023",
        TravelEndDate: "12/25/2023",
        Airline: "Emirates",
        EstimatedAirfare: 6500,
        Hotel: "Hotel grand",
        EstimatedHotelCost: 2000,
        Requestor: "Johanna Lorenz",
        Approved: "No"
    },
    {
        TripTitle: "Event and conference travel",
        Destination: "Indiana,UK",
        TravelStartDate: "12/15/2023",
        TravelEndDate: "12/18/2023",
        Airline: "Alaska Air",
        EstimatedAirfare: 6000,
        Hotel: "Indiana Hotel",
        EstimatedHotelCost: 1500,
        Requestor: "Lidia Holloway",
        Approved: "Yes"
    },
    {
        TripTitle: "Internal meetings and visiting offices",
        Destination: "Georgia,UK",
        TravelStartDate: "12/27/2023",
        TravelEndDate: "12/30/2023",
        Airline: "Emirates",
        EstimatedAirfare: 6500,
        Hotel: "Hotel grand",
        EstimatedHotelCost: 2000,
        Requestor: "Johanna Lorenz",
        Approved: "No"
    },
    {
        TripTitle: "Company retreats",
        Destination: "Bengaluru,India",
        TravelStartDate: "1/5/2024",
        TravelEndDate: "1/11/2024",
        Airline: "SouthWest",
        EstimatedAirfare: 800,
        Hotel: "Hotel Royal Orchid Bangalore",
        EstimatedHotelCost: 1200,
        Requestor: "Lynne Robbins",
        Approved: "No"
    },
    {
        TripTitle: "Client meetings",
        Destination: "Austria,UK",
        TravelStartDate: "1/20/2024",
        TravelEndDate: "1/27/2024",
        Airline: "Japan Airlines",
        EstimatedAirfare: 7500,
        Hotel: "Hotel Rand",
        EstimatedHotelCost: 1500,
        Requestor: "Johanna Lorenz",
        Approved: "No"
    },
    {
        TripTitle: "Transfers and offshore work",
        Destination: "New York,UK",
        TravelStartDate: "1/31/2024",
        TravelEndDate: "2/5/2024",
        Airline: "British Airways",
        EstimatedAirfare: 5500,
        Hotel: "Hotel Mela Times Square",
        EstimatedHotelCost: 1800,
        Requestor: "Joni Sherman",
        Approved: "Yes"
    },
    {
        TripTitle: "Bleisure travel",
        Destination: "Indiana,UK",
        TravelStartDate: "2/15/2024",
        TravelEndDate: "2/21/2024",
        Airline: "Alaska Air",
        EstimatedAirfare: 6000,
        Hotel: "Indiana Hotel",
        EstimatedHotelCost: 1500,
        Requestor: "Lidia Holloway",
        Approved: "Yes"
    },
    {
        TripTitle: "Bleisure travel",
        Destination: "Indiana,UK",
        TravelStartDate: "2/15/2024",
        TravelEndDate: "2/21/2024",
        Airline: "Alaska Air",
        EstimatedAirfare: 6000,
        Hotel: "Indiana Hotel",
        EstimatedHotelCost: 1500,
        Requestor: "Lidia Holloway",
        Approved: "Yes"
    }
)

Where,

1. colTravel = Collection Name
2. TripTitle, Destination, TravelStartDate … = Collection Headers/Columns
3. “Company anniversary trip”, “Indiana,UK”… = Collection Values/Records

• Next, click on the App more commands (…) option and click on the Run OnStart option to get our collection.
• Then, go to the Variables section (x) -> Expand Collections drop-down to find collections, as shown below.

• Also, if you want to see your created collection, click the View Table option and select the respective collection like below.

• But here, we need to group the two columns into a single record and add a nested table to display the records in another collection (colDestination).
  • To do so, Add a Button control on the screen and set its OnSelect property to the code below:

OnSelect = ClearCollect(
    colDestination,
    GroupBy(
        colTravel,
        "Destination",
        "Airline",
        "GroupDestination"
    )
)

Where,

1. colDestination = It is a second collection name
2. GroupBy = This function helps us to group the multiple records in a single record
3. colTravel = It is the first name of the Power Apps collection
4. “Destination”, and “Airline” = These are the columns which we want to group
5. “GroupDestination” = It is the name of the nested table

• Now, Save, Publish, and Preview the app. Then, click on the Button control. Again, check the collections under the Variable section. There, we will get another recently created collection like the one below.

• Once you click on any nested table [GroupDestination], you will get the grouped data with respected columns, as shown below.

This is all about how to use a GoupBy() function in the Power Apps collection.

Power Apps Collection Filter GroupBy()

This section will discuss filtering data on a Power Apps collection group record. Here, I will take the same example to filter the destination based on the country.

• For example, I have a Power Apps collection, i.e., [colTravel]. In this collection, I have different records. However, as shown below, I want to filter the country based on “UK” and create another collection [DestinationByUK].

To achieve it, follow the below steps.

• Open the Power Apps Canvas app -> Add a Button control -> Set its OnSelect property to the code below.

OnSelect = ClearCollect(
    DestinationByUK,
    Filter(
        colTravel,
        "UK" exactin Destination
    )
)

Where,

1. DestinationByUK = Name of the collection
2. colTravel = It is the name of the collection that we have created
3. “UK” exactin Destination = We can filter the destination that contains the country “UK”

• Then, click the Button control and check the collections under the Variable section. There, we will get another collection, i.e., DestinationByUK. Next, click on the View Table option to display results like below.

This is how we can filter group data on the Power Apps collection.

Power Apps Collection Ungroup()

Now, we will see how to ungroup the group records from a Power Apps collection.

• For example, I have a Power Apps group collection, i.e., [colDestination]. In this collection, I have grouped the two columns, i.e., [Airline] and [Destination], in a single record [GroupDestination].
• Now, I want to create a collection (colDestinationUngroup) that ungroups the grouped record like below.

To do so, follow the below-mentioned steps. Such as:

• Open the Power Apps Canvas app -> Select a screen where you want to add a Button control -> Set its OnSelect property to the code below.

OnSelect = ClearCollect(
    colDestinationUngroup,
    Ungroup(
        colDestination,
        "GroupDestination"
    )
)

Where,

1. colDestinationUngroup = Name of the collection
2. Ungroup = This function returns a table to breaking the group records in a Power Apps collection
3. colDestination = It is the name of the collection [Source] that we want to ungroup
4. “GroupDestination” = It is the name of the group

• Next, click the Button control. Go to Variables (x) -> Expand Collections -> Select colDestinationUngroup -> Click View Table to view the result.

This is how to ungroup the group records from a Power Apps collection using a Ungroup() function.

Power Apps Collection Group Aggregate Results

Here, we will see how to work with Power Apps Collection Group Aggregate Results.

For example, I have a Power Apps Collection, i.e. DestinationByUK. There are two number columns in this collection:

1. EstimatedAirfare
2. EstimatedHotelCost

Now, I would like to sum up these two columns, and the result will be displayed in a new column, i.e., Sum of the Estimated Cost.

Refer to the table below:

EstimatedAirfare	EstimatedHotelCost	Sum of the Estimated Cost [Output]
6000	1500	7500
5500	1800	7300
6500	2000	8500
6000	1500	7500
6500	2000	8500

To achieve the above example, follow the below steps.

• Open the Power Apps Canvas app -> Select a screen where you want to add a Button control -> Set its OnSelect property to the code below:

OnSelect = ClearCollect(
    colTravelSum,
    AddColumns(
        DestinationByUK,
        "Sum of the Estimated cost",
        Sum(
            EstimatedAirfare,
            EstimatedHotelCost
        )
    )
)

Where,

1. colTravelSum = Provide a new Collection Name
2. DestinationByUK = Collection name that we have created before
3. “Sum of the Estimated cost” = Specify the name of the new column to display the aggregate results
4. EstimatedAirfare, EstimatedHotelCost = These are the two columns that we want to calculate the sum

• Then, click the Button control. Go to Variables (x) -> Expand Collections -> Select colTravelSum -> Click View Table to view the result.

This is how we can get a Power Apps collection group aggregate results.

Additionally, you may like some more Power Apps tutorials:

• Remove Multiple Columns From Power Apps Collection
• Power Apps Collection vs Table
• How to Add a Lookup Column to Power Apps Collection
• How to Remove Duplicate Rows in Power Apps Collection

Conclusion

This Power Apps tutorial taught us all about the Power Apps collection GroupBy() function and how to use a GroupBy() function within a Power Apps collection.

Also, we covered how to filter group data on the Power Apps collection and ungroup the group record from a Power Apps collection using a Ungroup() function. And finally, we discussed how to get a Power Apps collection group aggregate results.