Power Apps GroupBy Function example

In this Power Apps Tutorial, We will see what is Power Apps GroupBy function, What are Power Apps Ungroup function and its syntaxes.

Also, By taking some simple scenarios, We will discuss the below topics that are related to the PowerApps GroupBy function.

Power Apps groupby
Power Apps groupby sum
PowerApps groupby SharePoint list
PowerApps groupby count
PowerApps groupby countrows
PowerApps groupby choice column

Table of Contents

PowerApps GroupBy

Power Apps GroupBy is a function that helps to return a table with items grouped together based on the values in one or multiple columns.
Similarly, the PowerApps Ungroup function is directly opposite of the GroupBy process. This function helps to break into separate records that were grouped together and return a table.
Basically, by using the GroupBy function, you can group the records, modify the table that it returns, and then you can ungroup records by using UnGroup function. Follow this below approach as:

You can use GroupBy function
You can use the Filter function to remove the grouped records
You can use the Ungroup function

If the original table contains blank records, then the GroupBy function will not work.
PowerApps GroupBy and Ungroup function do not modify a table instead they take a table as an argument and return a different table.

Read: Power Apps Image Control

PowerApps GroupBy Function Syntax

Below represents the syntax of Power Apps GroupBy Function:

Syntax:

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

Where,

Table = This is required that defines a table to be grouped
ColumnName(s) = This is required. The column names in Table by which to group records. These columns become columns in the resulting table.
GroupColumnName = Required. The column name for the storage of record data not in the ColumnName(s).

NOTE:
One important thing you need to keep on that, For SharePoint and Excel data sources that contain column names with spaces, specify each space as “x0020”. For example, specify “Column Name” as “Column_x0020_Name”.

PowerApps Ungroup Function Syntax

Below represents the syntax of Power Apps Ungroup Function:

Syntax:

Ungroup( Table, GroupColumnName )

Where,

Table = This is required that specifies a Table to be ungrouped.
GroupColumnName = This is also required. The column contains the record data setup with the GroupBy function.

Read: How to use Power Apps for free

PowerApps groupby sum

In this scenario, We will see how to calculate the sum of group by value in Power Apps.

On the PowerApps screen, I have two button inputs named Create Collection and Sum of Value. When the user will tap on the first button (Create Collection), then the collection will create.
The collection is having some Gadget expenses details like the below screenshot. To create this collection, Select button control and apply this below formula on its OnSelect property as:

OnSelect = ClearCollect(
    myGadgetExpenses,
    {
        Date: Date(
            2021,
            3,
            3
        ),
        Item: "Laptop",
        Value: 50050
    },
    {
        Date: Date(
            2021,
            3,
            3
        ),
        Item: "Mobile",
        Value: 9000
    },
    {
        Date: Date(
            2021,
            3,
            4
        ),
        Item: "Mobile",
        Value: 17550
    },
    {
        Date: Date(
            2021,
            3,
            5
        ),
        Item: "Headphone",
        Value: 1500
    },
    {
        Date: Date(
            2021,
            3,
            5
        ),
        Item: "Laptop",
        Value: 45670
    },
    {
        Date: Date(
            2021,
            3,
            6
        ),
        Item: "Ipod",
        Value: 8080
    }
);

Where,

myGadgetExpenses = Collection name

Next, We need to calculate the sum of the value of gadget expenses. To do this, select the second button (Sum of Value) and apply this below code on its OnSelect property as:

OnSelect = ClearCollect(
    myGadgets,
    DropColumns(
        AddColumns(
            GroupBy(
                myGadgetExpenses,
                "Item",
                "GroupedItems"
            ),
            "Sum of Value",
            Sum(
                GroupedItems,
                Value
            )
        ),
        "GroupedItems"
    )
);

Where,

myGadgets = Specify the collection name
myGadgetExpenses = Previous collection name that you have created using the first button
Item, Sum of Value = Header of this collection

Now save and preview the app. Click on the first button (Create Collection) and then tap to the second button (Sum of Value). Go to the collection, there you can see the total sum of each gadget expenses as shown in the below screenshot.

This is an example of groupby sum in PowerApps.

Power Apps groupby SharePoint list

Here we will discuss how we can work with PowerApps groupby in a SharePoint list.

On the PowerApps screen, I have a gallery that will display some records pulled from a SharePoint list. I would like the data to be grouped by the DisplayName of a person Field in that specific list.
My SharePoint List name is SharePoint Training Course and it has a Person field named BookAuthor.
To work on this, you need to add the field that you want to group by as its own column.

PowerApps groupby SharePoint list — Power Apps groupby SharePoint list

To work on this, we need to add this below formula on gallery’s Items property as:

Items = GroupBy(
    AddColumns(
        'SharePoint Training Course',
        "UserDisplayName",
        BookAuthor.DisplayName
    ),
    "UserDisplayName",
    "GroupByUsersDisplayName"
)

Where,

‘SharePoint Training Course’ = SharePoint List Name
“UserDisplayName” = Specify a new column name
BookAuthor = This is the SharePoint Person field. As we want to display this field with the person display name, so we need to write the choice column name with “.DisplayName”
“GroupByUsersDisplayName” = Group name

Once you will save and preview the app, you can see the grouped person field will appear in the gallery control as shown in the above screenshot.

This is an example of Power Apps groupby SharePoint list.

PowerApps groupby count

Do you want to count any distinct item or value in PowerApps group? Refer to this below example.

There is a SharePoint list named Gadget Details. This list has two columns as:

Gadget Name = By default, this is the Title column. I just renamed it to Gadget Name.
Brand = This column is a single line of text data type

Here in the below screenshot you can see there are some records. Now I would like to count the distinct number of items that are present in the list.

At first, On the PowerApps screen, Insert a Gallery control and apply this below code on its Items property as:

Items = AddColumns(
    GroupBy(
        'Gadget Details',
        "Title",
        "Grouped"
    ),
    "GadgetCount",
    CountRows(Grouped)
)

Where,

‘Gadget Details’ = SharePoint List name
“Title” = SharePoint List column that you want to count

Insert a Label control and set this below code to its Text property as:

Text = "Gadget Count: " & Gallery2.Selected.GadgetCount

Where,

Gallery2 = Gallery control name
GadgetCount = Column name that you have created in the gallery’s Items property

Now save and preview the app. Once the user will select any item from the gallery control, then the total number of counts will appear in the label control as in the above screenshot.

PowerApps groupby countrows

In this scenario, We will see how to work with the PowerApps group by count rows. Also, We will see how to count records with multiple instances.

There is a PowerApps Collection named GadgetCollection. This collection has one column named GadgetName.
It has some records like Laptop, Mobile, Tablet, etc. This is having some multiple records with the same name i.e. Laptop 3 times, Mobile 2 times, and Tablet 1 time.
Now I would like to count the total number of gadgets that should be like the below table as:

Gadget Name	Count
Laptop	3
Mobile	2
Tablet	1

You can see the PowerApps collection (GadgetCollection) in the below screenshot.

Now to count the records with multiple instances, Insert a Data table and apply this below formula on its Items property as:

Items = AddColumns(
    GroupBy(
        GadgetCollection,
        "GadgetName",
        "ByName"
    ),
    "Count",
    CountRows(ThisRecord.ByName)
)

Where,

GadgetCollection = PowerApps Collection name
GadgetName = Column name from the specific collection
Count = Specify the column name that will have the total number of count of each gadget

Once you will save and preview the app, you can see the total count of multiple instances in the data table as shown in the below screenshot.

PowerApps groupby choice column

Do you want to work with the SharePoint Choice column using the PowerApps GroupBy function? Below represents a simple scenario.

I have a SharePoint List named Event Registration Details. This list has a Choice column as Department that is having these below choice values:
- IT
- HR
- FINANCE

Here I would like to do a group-by with this SharePoint Choice column (Department).
On the PowerApps screen, Insert a Vertical gallery control and apply this below code to its Items property as:

Items = GroupBy(
    AddColumns(
        'Event Registration Details',
        "DepartmentGroup",
        Department.Value
    ),
    "DepartmentGroup",
    "EventGroup"
)

Where,

‘Event Registration Details’ = SharePoint List Name
“DepartmentGroup” = Here you need to provide a new field name
Department = SharePoint Choice column
“EventGroup” = Specify the group name

Once you will save and preview the app, you can see the group by choice column will appear in the PowerApps gallery control as shown above.

Also, you may like these below PowerApps tutorials:

In this PowerApps Tutorial, We discussed what is PowerApps GroupBy function, What are PowerApps Ungroup function and its syntaxes.

Also, By taking some simple scenarios, We covered the below topics that are related to the PowerApps GroupBy function.

PowerApps groupby gallery
PowerApps groupby sum
PowerApps groupby SharePoint list
PowerApps groupby count
PowerApps groupby countrows
PowerApps groupby choice column

Bijay Kumar

I am Bijay a Microsoft MVP (8 times – My MVP Profile) in SharePoint and have more than 15 years of expertise in SharePoint Online Office 365, SharePoint subscription edition, and SharePoint 2019/2016/2013. Currently working in my own venture TSInfo Technologies a SharePoint development, consulting, and training company. I also run the popular SharePoint website EnjoySharePoint.com