Power Apps Gallery Group By [With Real Examples]

Organizing a list of items into groups helps to clarify their meaning. However, in a Power Apps gallery, we can only display a list. So, how can we create groups in a gallery alternatively?

So in this Power Apps Tutorial, we will learn about Power Apps GroupBy() and how to use this function within the Power Apps gallery based on different scenarios. Also, we will work with the below subtopics:

  • Power Apps GroupBy()
  • Power Apps GroupBy() syntax
  • Power Apps gallery GroupBy() column
  • Power Apps gallery Group By() and sum
  • Power Apps gallery Group By() count rows
  • Power Apps gallery Groupby() person field
  • Power Apps gallery Groupby() collection
  • Power Apps gallery GroupBy() filter
  • Power Apps gallery GroupBy() Choice Column
  • Power Apps gallery GroupBy() lookup column
  • Power Apps nested gallery GroupBy()
  • Power Apps gallery GroupBy quantity
  • Power Apps gallery GroupBy column and sum

Before working with GroupBy() function in the Power Apps gallery, first, we will understand what is Power Apps gallery GroupBy() is.

Power Apps GroupBy()

  • Power Apps GroupBy is a function used to return a table containing items that have been grouped based on the values in one or more columns.
  • Likewise, the Power Apps Ungroup function is the inverse of the GroupBy methodology. This function assists in separating records that were previously grouped and returning a table.
  • The Power Apps GroupBy function allows you to group records and modify the table that they return.
  • The Power Apps GroupBy function will not work if the original table contains empty records.
  • The Power Apps GroupBy and UnGroup functions do not modify a table; instead, they take a table as an argument and return a different table.

Also Read: Power Apps Drop down Control [With Various Examples]

Power Apps gallery GroupBy()

We used a gallery within Power Apps to display the grouping data. This is known as Power Apps Gallery GroupBy.

Power Apps GroupBy() and UnGroupBy() syntax

Now, we will see what the syntaxes and properties are in the Power Apps GroupBy() and UnGroupBy().

Power Apps GroupBy() syntax

The syntax of Power Apps GroupBy Function is as follows:

GroupBy(DataSourec, Column_Name1 [, Column_Name2, ... ], Group_ColumnName)

Where,

  • DataSource: Required. The name of the data source
  • Column_Name(s): Required. The column names in the Table will be used to group the records. These columns are converted into columns in the resulting table.
  • Group_ColumnName: Required. The column name for collecting record data that is not in the ColumnName(s).

Power Apps UnGroup() syntax

The Power Apps UnGroup() syntax The following are the functions:

Ungroup( Source, GroupColumnName )

Where,

  • Source: The name of the table or collection to ungroup.
  • GroupColumnName: The name of the column to use as a main basis for the ungroup operation.

Also check: Power Apps Dropdown Control with SharePoint

Power Apps gallery GroupBy() column

In this section, we’ll look at how to group a column in the Power Apps gallery. Before we begin, we must first create a SharePoint list to group columns based on SharePoint or another data source.

So, we have prepared a SharePoint list named “Job Openings” with some random columns and data that are shown below:

Power Apps groub by column
Power Apps group by column

Let’s use the above SharePoint list in a Power Apps gallery to perform GroupBy(). To do this, the following steps are:

  • Sign in to the Power Apps with your Office365 ID or Microsoft365 User ID.
  • In the Power Apps environment, click on the +Create > Blank app > Blank Canvas app > Create.
  • Give a name to the app and then choose a format (example: tablet).
  • Again, press Create.
Create a blank canvas on Power APPS
Create a blank canvas on Power Apps

It will create an app with a blank screen. On that default screen, we will add a gallery to display the data. To add the gallery, the following steps are:

  • On the Power Apps screen, go to Insert > Gallery.
  • Select a gallery as per your need. Suppose we choose, Blank Vertical Gallery. It will create a blank gallery on the screen.
Create a blank gallery on Power Apps screen
Create a blank gallery on the Power Apps screen

Now it’s time to add the data source to this blank gallery.

  • Navigate to Data > Add data.
  • Search for ‘SharePoint‘ on the search bar and select your site connection.
  • Choose your SharePoint site to connect.
  • Choose a list, i.e., job openings.
  • Connect.

Now we can find the data sources go to Data on the Power Apps.

Power Apps connect to data source
Power Apps connect to the data source

In the gallery, set the layout as “Title.” Select the gallery, on the right properties panel, click on the layout, and select the “Title” layout, so that it will display all the titles of the items.

Then Click on the DataSource and select the SharePoint list Name i.e. Job Openings.

Power Apps gallery GroupBy() column
Power Apps gallery GroupBy() column

Now we will group the above data by title. To do this, we need to insert the below expression in the gallery’s items property.

Items = GroupBy('Job Openings', "Title", "GroupbyTitle")

Where,

  • Job Openings: The name of the data source
  • Title: The SharePoint Column name
  • GroupbyTitle: The name of the group column name

Once the formula is applied, we can see the gallery is grouped title.

Power Apps gallery Group By column
Power Apps gallery Group By column

This is how to group by a SharePoint column in the Power Apps gallery.

Also Read: Automatically scroll Gallery control in PowerApps

Power Apps gallery GroupBy() and sum

Here, we will see how to convert a Power Apps gallery into a group of items and how to calculate the sum.

Suppose, we have a SharePoint list based on the product’s order that contains the product’s list and the quantity like below:

powerapps gallery group by and sum
powerapps gallery group by and sum

Now, we want to group the above list by title and evaluate their total order quantity. To work with this scenario, we have to build a Power Apps gallery using the above SharePoint list.

GroupBy and multiple Sum columns on Power Apps gallery
group by and multiple sum columns in the power apps gallery

To group the above data by product’s title and calculate the total quantity, we will insert the below expression on the gallery’s Items property.

Items = AddColumns(GroupBy('Orders List',"Title","GroupData"),"TotalQty",Sum(GroupData,OrderQty))

Where,

  • Orders List: The name of the SharePoint list
  • Title, OrderQty: The name of the SharePoint list
  • GroupData, TotalQty: The name of the group column name.
Power Apps gallery GroupBy and multiple Sum columns
Power Apps gallery GroupBy and multiple Sum columns

Next, select the label control that displays the quantity of the order, select that label, and insert the below expression on the label’s Text property.

Text = ThisItem.TotalQty

Now, we can see the items in the gallery are grouped by the title with a total order quantity like below:

Power Apps gallery GroupBy and calculate sum
Power Apps gallery Group By calculating the sum

This is how to use a group in a Power Apps gallery and calculate the sum.

Check: How to use Power Apps Gallery Dropdown

Power Apps gallery GroupBy() CountRows

In this section, we will see how to work with Power Apps gallery GroupBy() CountRows. That means it will count records with repeated versions within a data source.

Consider the Power Apps gallery above, which makes use of the SharePoint “Orders list.” To count the items, we’ll use the expression below on the gallery’s items property. It will group the items based on their titles and count the items based on their repeated versions.

Items = AddColumns(GroupBy('OrdersList',"Title","ByProductName"),"TotalCount",CountRows(ThisRecord.ByProductName))

Where,

  • ByProductName, TotalCount: The name of the group column name.
Power Apps gallery GroupBy() count rows
Power Apps gallery GroupBy() count rows

Similarly, insert a label control into the gallery and set the below expression on the label’s text property to display the count.

Text = ThisItem.TotalCount

Where TotalCount is the name of the group column that holds the count of each item.

powerapps gallery group by count rows
Powerapps gallery group by count rows

This is how to calculate the count row on the Power apps gallery.

Check out: Power Apps Data Table [Complete Guide]

Power Apps gallery GroupBy() person field

Here, we will see how to group a person’s field by their display name. Suppose, we have a SharePoint list named ‘Projects‘ having a bunch of records including a people column.

Power Apps gallery GroupBy person field
Power Apps gallery GroupBy person field

Using the above SharePoint list we will prepare a Power Apps gallery that will display the person’s field only like below:

powerapps group by person field
Power Apps group by person field

To group the above gallery by person field, we need to insert the below expression within the gallery’s items property.

Items= GroupBy(AddColumns(Projects,"ProjectHandlerName",ProjectHandler.DisplayName),"ProjectHandlerName","GroupbyProjectHandler")

Where,

  • Projects: The name of the Sharepoint list
  • ProjectHandlerName: Give a name to the new column
  • ProjectHandler.DisplayName: The display name of the SharePoint people column
  • GroupbyProjectHandler: The name of the group column
Power Apps Gallery GroupBy Person Field Display name
Power Apps Gallery GroupBy Person Field Display name

Similarly, on the project handler’s label control, set the below expression on the Text property.

Text = ThisItem.ProjectHandlerName
Group By Person field Displayname in Power Apps Gallery
Group By Person field Display name in Power Apps Gallery

Now, we can see the gallery is the group by person field like below:

GroupBy Person field Displayname in Power Apps Gallery
GroupBy Person field Displayname in Power Apps Gallery

This is how to group by a SharePoint person field in the Power Apps Gallery.

Read Power Apps combo box with Office 365 users

Power Apps gallery Groupby() collection

Here, we will see how to group a collection within the Power Apps gallery. To work with this, we will create a collection on the Power Apps screen having some columns and data such as Cities, Dates, and Sales.

Also, we will place a button on the screen, when the user clicks on that button it will convert the regular data to grouped data. To work with this, the following steps are:

  • On the Power Apps screen, insert the below expression on the OnVisible property to create a collection.
OnVisible = ClearCollect( ColSales,
    { City: "Bristol",Date: Date(2022,8,25), Sales: 35000},
    { City: "Irpin", Date: Date(2022,8,25), Sales: 32000},
    { City: "Lyon", Date: Date(2022,8,25), Sales: 65000},
    { City: "Rome",Date: Date(2022,8,26), Sales: 40600},
    { City: "Paris", Date: Date(2022,8,26), Sales: 23000},
    { City: "Haifa", Date: Date(2022,8,27), Sales: 10000},
    { City: "Barcelona", Date: Date(2022,8,27), Sales: 10020},
    { City: "Bonn", Date: Date(2022,8,28), Sales: 14000}
) 

Where,

  • ColSales: The name of the collection
  • City, Date, Sales: The column name of the collection.
Power Apps gallery Groupby collection
Power Apps gallery Groupby collection
  • We can find our collection on the Power Apps by following the steps. On the Power Apps screen, go to View > Collections.
View collection on Power Apps
View collection on Power Apps
  • To convert the above collection to a group, we will add a button to the screen, give a name to the button, and set the below formula on the OnSelect property.
OnSelect = ClearCollect( ColCities, GroupBy(ColSales ,"Date", "GroupDate" ) )

Where,

  • ColCities: The name of the new collection
  • Date: The collection column which we want to group
Power Apps gallery group by collection
Power Apps gallery group by collection

Let’s save, publish, and play the app in preview mode. Then click on the Button. Again, check the collection under view. There we will get another new collection i.e., ColCities.

Power Apps gallery group by records
Power Apps gallery group by records

When we click on any GroupDate, we can see the grouped data with cities and sealed like below. (For example, 8/25/2022)

Group by collection on Power Apps gallery
Group by collection on Power Apps gallery

This is how to group by a collection on the Power Apps gallery.

Also read: Power Apps Radio Button

Power Apps gallery GroupBy() filter

Following that, we’ll look at how to filter data on a group record. Let’s use the above group data to filter cities based on sales. That is, we will filter the data so that only the filtered cities are displayed. To do this, the following steps are:

  • On that screen, we will add another button control.
  • Rename the button as ‘CitiesBySales Filter‘.
  • On the OnSelect property insert the below expression to filter the cities on a specific letter by sales.
OnSelect = ClearCollect(CitiesBySales,Filter(ColSales,"b" in City))

Where,

  • CitiesBySales: The name of the collection
  • ColSales: The name of the Collection that we have created.
  • “b” in City: Filter the cities that contain the letter ‘b
Power Apps gallery group by filter
Power Apps gallery group by filter

Next, press the Alt key + Button. We can see the button is clicked. Now, go to Collection under View and check the filtered group data i.e. CitiesBySales.

GroupBy filter in Power Apps Gallery
GroupBy filter in Power Apps Gallery

This is how to filter group data on the Power apps gallery collection.

Power Apps gallery GroupBy Choice Column

Here we will see how to work with the Power Apps gallery group by choice column. Suppose, we have a SharePoint list named ‘Order Lists‘ having a group column i.e., Company.

Power Apps gallery GroupBy Choice Column
Power Apps gallery GroupBy Choice Column

Now, we will connect this SharePoint list to the Power Apps. Also, we will create a gallery on the screen to display the list of the company’s values that come from the choice column like below:

Power Apps gallery GroupBy SharePoint Choice Column
Power Apps gallery GroupBy SharePoint Choice Column

Next, insert the below expression on the Gallery’s Items property to group the above data.

Items = GroupBy(AddColumns('Orders List',"GroupByCompany",Company.Value),"GroupByCompany","CorporateGroup")

Where,

  • Orders List: The name of the SharePoint Data source.
  • GroupByCompany: The name of the new column.
  • Company.Value: The name of the Choice column.
  • CorporateGroup: The name of the new group.
power apps gallery groupby choice field
power apps gallery group by choice field

Now, the gallery will display the group choice field like below:

power apps gallery group by choice field
power apps gallery group by choice field

This is how to group by the choice field within the Power Apps gallery.

Check out: Power Apps in SharePoint

Power Apps gallery GroupBy() lookup column

Here, we will see how to work with the Power Apps gallery group by lookup column. To work with this requirement, we have prepared 2 SharePoint lists such as:

  1. Vendors list-it contains only a list of the vendor’s titles.
Power Apps gallery GroupBy() lookup column
Power Apps gallery GroupBy() lookup column

2. vendors– It contains a lookup field named ‘Vendors‘ (lookup from the title of the Vendors list) and a date column i.e., Date.

Power Apps gallery GroupBy lookup column
Power Apps gallery GroupBy lookup column

Now, we will build a Power Apps gallery that is grouped by the above lookup column. To work with this, we will attach both of these data sources to the Power Apps gallery.

Then on the Power Apps screen, add a blank gallery and set the items as “vendors”. Also, add a label control to the gallery and set the below expression on the label’s Text property to display the above lookup column like below:

Text = ThisItem.Vendors.Value

Where Vendors is the name of the lookup column.

Power Apps gallery GroupBy and lookup field
Power Apps gallery GroupBy and lookup field

To make a group by lookup field, we need to insert the below expression on the gallery’s Items property.

Items =GroupBy(AddColumns(vendors, "Vendors_Name", Vendors.Value),"Vendors_Name","GroupVendors")

Where,

  • vendors: The name of the data source
  • Vendors_Name: The name of the new column.
  • Vendors_Value: The name of the lookup column name
  • GroupVendors: The name of the group column name
GroupBy with Lookup column in Power Apps gallery
GroupBy with Lookup column in Power Apps gallery

Now, select the label and replace it with the below expression on the Text property. So that it will group the lookup column.

Text = ThisItem.Vendors_Name

Where the Vendors_Name is the new column name that we have created.

GroupBy SharePoint Lookup Column Power Apps gallery
GroupBy SharePoint Lookup Column Power Apps gallery

This is how to group by SharePoint lookup column in the Power Apps gallery.

Power Apps gallery GroupBy() quantity or Count selected item

In this section, we will see how to work with Power Apps gallery GroupBy() quantity or Count selected item. Suppose, we have a bulk record and we want to calculate the quantity of each selected item. To work with this, the following steps are:

  • We are going to use the previous Power Apps gallery by using a SharePoint list named Job Openings.
Power Apps gallery GroupBy quantity
Power Apps gallery GroupBy quantity
  • Next, insert the below expression on the gallery’s items property.
Items = AddColumns(GroupBy('Job Openings',"Title","GroupedTitle"),"Opening_Count",CountRows(GroupedTitle))

Where,

  • Job Openings: The name of the SharePoint list
  • Title: The name of the SharePoint column we want to group
  • GroupedTitle: The name of the group column
  • Opening_Counts: The name of the new column
Count selected item on Power Apps gallery group by
Count selected items on the Power Apps gallery group by
  • Add a label control to the screen to display the count of the selected item. Select the label and insert the below expression on the Text property.
Text = "The Total count of the selected item is :" & 'JobOpenings Gallery'.Selected.Opening_Count
Power Apps gallery GroupBy Count selected item
Power Apps gallery GroupBy Count selected item

To identify the selected item on the gallery, insert the below expression on the TemplateFill property of the gallery.

TemplateFill = If(ThisItem.Title = 'JobOpenings Gallery'.Selected.Title, LightGray, White)

Now save, publish, and play the app. When we select any item from the gallery, it will be identified with a color, i.e., light gray, as well as the quantity displayed on the Power Apps gallery.

Power Apps gallery Group By quantity
Power Apps gallery Group By quantity

This is how to display the quantity of a selected item in a group in the Power Apps gallery.

Also read: Power Apps SharePoint Button

Power Apps nested gallery GroupBy()

In this section, we will see how to work with Power Apps nested gallery Groupby(). That means we will create a nested gallery view in PowerApps by using the GroupBy function. To Work with this, the following steps are:

  • On Power Apps, we will create a collection as a data source for the galleries in this nested gallery application. So insert the below expression on the app’s OnStart property.
OnStart = ClearCollect(
    collProduct,
    {
        Product: "Apple",
        ModelNumber: "iPhone14",
        Manufacture: "China",
        pincode: "89889"
    },
    {
        Product: "Samsung",
        ModelNumber: "S21FE",
        Manufacture: "India",
        pincode: "01252"
    },
    {
        Product: "OnePlus",
        ModelNumber: "10 Pro",
        Manufacture: "China",
        pincode: "12535"
    },
    {
        Product: "Apple",
        ModelNumber: "iPhone13",
        Manufacture: "USA",
        pincode: "46552"
    },
    {
        Product: "OnePlus",
        ModelNumber: "9 Pro",
        Manufacture: "China",
        pincode: "85311"
    },
    {
        Product: "Apple",
        ModelNumber: "iPhone12ProMax",
        Manufacture: "USA",
        pincode: "89656"
    }

)

Where,

  • collProduct: The name of the collection
  • Product, ModelNumber, Manufacture, pincode: The name of the collection’s column.
Power Apps nested gallery GroupBy
Power Apps nested gallery GroupBy
  • To check the collection on the Collections section under View.
View collection on Power Apps
View collection on Power Apps
  • Add the below expression on the Screen’s Onvisible property to create a group using the above collection.
OnVisible = ClearCollect(collGroupProduct,GroupBy(collProduct,"Product","GroupProduct"))

Where,

  • collGroupProduct: The name of the new collection
  • collProduct: The name of the collection that has been created
  • Product: The name of the collection’s column that we want to group
GroupBy in Power Apps nested gallery
GroupBy in Power Apps nested gallery
  • To check the group collection, go to View > Collections.
Group by collection on Power Apps
Group by collection on Power Apps
  • Next, add a gallery to the screen and rename it Parent Gallery. Set the collGroupProduct as the gallery’s items property.
Items = collGroupProduct

Where collGroupProduct is the name of the collection that we have created.

  • Add a rectangle and a label control to the gallery. Give a text such as ‘Product‘ to the label control.
  • Also, add another label control and place it on the rectangle to display the collection’s product name (from the collection).
  • Inset the below expression on the other label’s Text property to display the product name in the gallery.
Text = ThisItem.Product
Power Apps nested gallery with GroupBy
Power Apps nested gallery with GroupBy
  • Similarly, on the parent gallery, add a rectangle, and then add 3 label controls for Manufacture, Product, and Pin code that are shown below:
Power Apps group by function and nested gallery
Power Apps group by function and nested gallery
  • Inside the Parent gallery, we will add another gallery to display the GroupProduct (the name of the group column we created initially). Select the template of the parent gallery and insert another blank vertical gallery.
  • Rename the gallery as a Child gallery and set the items property as below expression:
Items = ThisItem.GroupProduct
How to create Nested Gallery in Power Apps using group by
How to create Nested Gallery in Power Apps using group by
  • Inside the Child gallery, add 3 label controls to map them with Manufacture, Model, and Pin Code.
Nested Gallery and Group By Power Apps
Nested Gallery and Group By Power Apps
  • To see the total product in each child gallery, we will insert the below expression on the label’s text property that only displays the product name.
Text = ThisItem.Product & "("& " " & CountRows('Child Gallery'.AllItems)&" " &")"
Create a nested gallery group by on Power Apps
Create a nested gallery group on Power Apps

This is how to create a nested gallery group on Power Apps.

Also, you may like the below Power Apps tutorials:

Conclusion

From this Power Apps Tutorial, we learned all about the work with GroupBy() function within the Power Apps gallery. Also, we have discussed below topics such as:

  • What is Power Apps gallery GroupBy()?
  • What is Power Apps gallery GroupBy() syntax?
  • How to GroupBy a Power Apps gallery column?
  • How to do Power Apps gallery GroupBy and calculate the sum?
  • How to calculate the count rows using the Power Apps gallery GroupBy?
  • How to use a GroupBy() person field on the Power Apps gallery?
  • How to create a group by collection on the Power Apps gallery?
  • How to do GroupBy using filter power Apps gallery?
  • How to use Group By function within the Choice Column on the Power Apps gallery?
  • How to do the GroupBy lookup column in the Power Apps gallery?
  • How to calculate the GroupBy quantity in the Power Apps gallery?
  • How to create a nested gallery using Power Apps GroupBy?
>