How to Use Power Apps Collection GroupBy?

Are you interested in learning how to use a Power Apps collection GroupBy? In this Power Apps tutorial, I will explain everything related to the Power Apps collection GroupBy. Such as:

  • Power Apps Collection UnGroup
  • Power Apps Collection Filter GroupBy
  • Working with Power Apps Collection Group Aggregate Results

Power Apps Collection GroupBy()

The Power Apps GroupBy() function is a useful tool to group records in a table based on values in one or more columns.

Moreover, we can group all the records into a single entry that could serve as a nested table within the Power Apps collection.

Power Apps GroupBy() Syntax:

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

Where,

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

For example: I have two Power Apps collections, i.e., [colPatients] and [colDiseases], as in the screenshot below.

The first collection contains the below headers/columns.

Column NameData Type
PatientIDText
PatientNameText
DateOfBirthDate and time
DiseaseText
DoctorPerson
Power Apps Collection GroupBy

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

How to Use Power Apps Collection GroupBy

To work around this, follow the below-mentioned steps. Such as:

1. Open Power Apps with your credentials -> Create a Blank canvas app -> Select App object [From left navigation] and set its OnStart property to the code below.

OnStart = ClearCollect(
    colPatients,
    {
        PatientID: "SP001",
        PatientName: "Richard",
        DateOfBirth: "10/1/1995",
        Disease: "Influenza",
        Doctor: "Store Kane",
        DoctorFees: 2000,
        TotalFees: 5000
    },
    {
        PatientID: "SP002",
        PatientName: "Steven Rock",
        DateOfBirth: "2/17/2001",
        Disease: "Malaria",
        Doctor: "Chelsi Cooley",
        DoctorFees: 1000,
        TotalFees: 4000
    },
    {
        PatientID: "SP003",
        PatientName: "Lynee",
        DateOfBirth: "9/21/1998",
        Disease: "Lyme disease",
        Doctor: "Herman Boykin",
        DoctorFees: 1200,
        TotalFees: 4500
    },
    {
        PatientID: "SP004",
        PatientName: "Rosi Marky",
        DateOfBirth: "12/7/2000",
        Disease: "Malaria",
        Doctor: "Store Kane",
        DoctorFees: 1500,
        TotalFees: 5500
    },
    {
        PatientID: "SP005",
        PatientName: "Aaman Roy",
        DateOfBirth: "11/1/2002",
        Disease: "Malaria",
        Doctor: "Richard rone",
        DoctorFees: 2500,
        TotalFees: 5800
    }
)

Where,

  • colPatients = Power Apps Collection Name
  • PatientID, PatientName, DateOfBirth, etc… = Collection Headers/Columns
  • “SP001”, “Richard”, “10/1/1995”, etc… = Collection Records/Rows
How to Use GroupBy Power Apps Collection

2. Next, click on the App more commands () option and click on the Run OnStart option to get our collection.

See also  Power Apps Dataverse Choices [Complete Guide]

3. Then, go to the Variables section -> Expand Collections drop-down to find collections, as shown below.

How to Use a GroupBy Power Apps Collection

4. Here, we need to group the two columns into a single record and add a nested table to display the records in another collection [colDiseases]. To do so, Add a Button control on the screen and set its OnSelect property to the code below:

OnSelect = ClearCollect(
    colDiseases,
    GroupBy(
        colPatients,
        "Disease",
        "Doctor",
        "DoctorFees",
        "GroupDiseases"
    )
)

Where,

  • colDiseases = Collection name
  • GroupBy = This function helps us to group the multiple records in a single record
  • colPatients = Power Apps source collection
  • “Disease”, “Doctor”, and “DoctorFees” = These are the columns which we want to group
  • “GroupDiseases” = It is the name of the nested table
Power Apps GroupBy Collection

5. Now, Save, Publish, and Preview the app. When the user clicks on the Button control, the collection will added under the variable section.

6. Once you click on any nested table [GroupDiseases] you will get the grouped data with respected columns, as in the screenshot below.

How to Use the Power Apps Collection GroupBy

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

Power Apps Collection Ungroup

Next, we will see how to use a Power Apps collection UnGroup with a simple example:

Example:

I will also take the same grouped collection [colDiseases] for this example. Now, Now, I want to create a collection [colDiseasesUnGroup] that ungroups the grouped record like below.

Power Apps Collection UnGroup

To achieve it, follow the below steps.

1. On the Power Apps Screen -> Insert a Button control and set its OnSelect property to the code below.

OnSelect = ClearCollect(
    colDiseasesUnGroup,
    Ungroup(
        colDiseases,
        "GroupDiseases"
    )
)

Where,

  • colDiseasesUnGroup = Power Apps Collection Name
  • Ungroup = This function returns a table to breaking the group records in a Power Apps collection
  • colDiseases = It is the name of the collection [Source] that we want to ungroup
  • “GroupDiseases” = It is the name of the group
Power Apps UnGroup Collection

2. Next, click on the button control to get the respective collection [colDisesaseUnGroup] under the variables section below.

How to use Power Apps UnGroup Collection

This is how to ungroup the group records from a Power Apps collection.

See also  Power Apps Modern Slider Control - How to Use

Power Apps Collection Filter GroupBy

Now, we will discuss filtering data on a Power Apps collection group record. Here, I will also take the same collection [colPatients].

Also, I have created another collection named “colDeseasesByFilter“. In this collection, I have different records. However, as shown below, I want to filter the “Disease” column based on a specific disease like “Malaria” as in the screenshot below.

Power Apps Collection Filter GroupBy

To do so, on the Power Apps Screen -> Add a Button control -> Set its OnSelect property to the code below.

OnSelect = ClearCollect(
    colDeseasesByFilter,
    Filter(
        colPatients,
        "Malaria" exactin Disease
    )
)

Where,

  • colDeseasesByFilter = Name of the collection
  • Filter() = This function is used to find a set of records that match one or more criteria
  • colPatients = Power Apps Source Collection
Power Apps Collection GroupBy Filter

Save, Publish, and Preview the app. When the user clicks on the button control, the filter collection will added under the variables section like below.

How to use Power Apps Collection Filter GroupBy

This is how to use a Power Apps collection filter GroupBy.

Power Apps Collection GroupBy Aggregate Results

In the last, I will show you how to get the Power Apps collection GroupBy aggregate results with a simple scenario:

Scenario:

I will also take the same Power Apps collection [colPatients] for this example. Now, I would like to sum up two columns, i.e., [DoctorFees, and MedicalFees], and the result will be displayed in a new column, i.e., [TotalFees].

Refer to the below screenshot:

Power Apps Collection GroupBy Aggregate Results

To do so, on the Power Apps Screen -> Insert a Button control and set its Items property as:

OnSelect = ClearCollect(
    colTotalAmount,
    AddColumns(
        colPatients,
        "TotalFees",
        Sum(
            DoctorFees,
            MedicalFees
        )
    )
)

where,

  • colTotalAmount = Power Apps collection name
  • AddColumns() = This function is to help us to create a new column for a table
  • colPatients = Power Apps source collection
  • “TotalFees” = Name of the new column
  • Sum() = This function is used to calculate the sum arguments
  • DoctorFees, and MedicalFees = These are the two columns that we want to calculate the sum
Power Apps GroupBy Collection Aggregate Results

Once your app is ready, Save, Publish, and Preview the app. When the user clicks on the button control, the collection will added under the variables section with a new column, as shown below.

How to use Power Apps Collection GroupBy Aggregate Results

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

See also  PowerApps Twitter Connector

Conclusion

I trust this Power Apps tutorial taught us all about the Power Apps collection GroupBy() function, and we saw ungroup the group record from a Power Apps collection using a Ungroup() function.

Also, we discussed how to filter group data on the Power Apps collection and how to get a Power Apps collection group aggregate results.

You may also like:

>