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:
TripTitle
Destination
TravelStartDate
TravelEndDate
Airline
EstimatedAirfare
Hotel
EstimatedHotelCost
Requestor
Approved
  • 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.
Airline
Destination
GroupedDestination
Power Apps Collection GroupBy
  • Once the nested table [GroupDestination] is clicked, it will display the grouped records as shown below.
Power Apps Canvas app Collection GroupBy

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
How to use Power Apps Collection GroupBy
  • 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.
How to use a Power Apps Collection GroupBy
  • Also, if you want to see your created collection, click the View Table option and select the respective collection like below.
How to use the Power Apps Collection GroupBy
  • 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
How to use GroupBy Function in Power Apps Collection
  • 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.
How to use a GroupBy Function in Power Apps Collection
  • Once you click on any nested table [GroupDestination], you will get the grouped data with respected columns, as shown below.
How to use GroupBy Function in a Power Apps Collection

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].
How to filter Power Apps group collection

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”
Power Apps Collection Filter GroupBy
  • 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.
Power Apps Collection Filter using GroupBy Function

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.
How to ungroup the Power Apps collection grouped records

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
Power Apps Collection Ungroup
  • Next, click the Button control. Go to Variables (x) -> Expand Collections -> Select colDestinationUngroup -> Click View Table to view the result.
How to Ungroup a Power Apps Collection

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:

EstimatedAirfareEstimatedHotelCostSum of the Estimated Cost [Output]
600015007500
550018007300
650020008500
600015007500
650020008500
How to get group aggregate results in Power Apps collection

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
Power Apps Collection Group Aggregate Results
  • Then, click the Button control. Go to Variables (x) -> Expand Collections -> Select colTravelSum -> Click View Table to view the result.
Group Aggregate Results in Power Apps Collection

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

Additionally, you may like some more Power Apps tutorials:

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.