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:
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.
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.
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.
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.
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.
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:
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.
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.
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:
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.
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.
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.
Using the above SharePoint list we will prepare a Power Apps gallery that will display the person’s field only like below:
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
Similarly, on the project handler’s label control, set the below expression on the Text property.
Text = ThisItem.ProjectHandlerName
Now, we can see the gallery is the group by person field like below:
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.
- We can find our collection on the Power Apps by following the steps. On the Power Apps screen, go to View > Collections.
- 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
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.
When we click on any GroupDate, we can see the grouped data with cities and sealed like below. (For example, 8/25/2022)
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‘
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.
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.
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:
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.
Now, the gallery will display the group choice field like below:
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:
- Vendors list-it contains only a list of the vendor’s titles.
2. vendors– It contains a lookup field named ‘Vendors‘ (lookup from the title of the Vendors list) and a date column i.e., Date.
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.
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
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.
This is how to group by SharePoint lookup column in the Power Apps gallery.
Read Power Apps combo box filter
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.
- 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
- 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
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.
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.
- To check the collection on the Collections section under View.
- 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
- To check the group collection, go to View > Collections.
- 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
- Similarly, on the parent gallery, add a rectangle, and then add 3 label controls for Manufacture, Product, and Pin code that are shown below:
- 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
- Inside the Child gallery, add 3 label controls to map them with Manufacture, Model, and Pin Code.
- 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)&" " &")"
This is how to create a nested gallery group on Power Apps.
Also, you may like the below Power Apps tutorials:
- How to Remove Duplicates in Power Apps Dropdown
- How to Set Default Value in Power Apps Dropdown
- PowerApps stuck on getting your data
- Power Apps Navigate Function + 16 Examples
- Power Apps WeekNum and ISOWeekNum Function with Examples
- PowerApps form benefits for SharePoint
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?
After working for more than 15 years in Microsoft technologies like SharePoint, Office 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (9 times). I have also worked in companies like HP, TCS, KPIT, etc.