#PowerPlatformTip 32: 'Optimize with GroupBy'

TL;DR: For dependent dropdowns (category → subcategory), load the data once into a collection, then group it: GroupBy(colProducts, Category, CategoryRows). The first dropdown lists the groups, the second one simply uses cmbCategory.Selected.CategoryRows. No second query, no Filter() against the data source on every selection.

💡 Challenge

Dependent dropdowns are a classic: pick a category, then a subcategory, then a product. The usual approach filters the data source again in each dropdown, for example Filter(Products, Category = cmbCategory.Selected.Value). Every change of the first selection sends a new query, and building the category list with Distinct() on the data source is one more query.

✅ Solution

Load the rows once into a collection and let GroupBy() build the hierarchy in memory. GroupBy returns one record per category, with a nested table of all rows of that category. The second dropdown points to that nested table, so selecting a category needs no new query at all.

The lever is the single load into memory. GroupBy is what makes the dependent dropdowns trivial once the data is there.

🔧 How It’s Done

1. Load the data once.

🔸 In the screen’s OnVisible (or App.OnStart):

ClearCollect(
    colProducts,
    ShowColumns(Products, Category, SubCategory, Title)
)

🔸 Only load the columns the dropdowns need. If the data source holds more rows than the app’s data row limit (500 by default), filter it down to the relevant rows with a delegable Filter() first, for example only active products.

2. Group by the first level.

🔸 Items of the category dropdown cmbCategory:

GroupBy(colProducts, Category, CategoryRows)

🔸 Set the dropdown to display the Category column. Each record also carries a column CategoryRows with all rows of that category.

3. Use the nested table for the second level.

🔸 Items of the subcategory dropdown cmbSubCategory:

Distinct(cmbCategory.Selected.CategoryRows, SubCategory)

🔸 Distinct returns a single-column table with the column Value. For a third level (the product), use:

Filter(cmbCategory.Selected.CategoryRows, SubCategory = cmbSubCategory.Selected.Value)

All three formulas work on the collection, so they don’t send requests to the data source.

🎉 Result

The dropdowns react immediately when the user changes a selection, because everything after the first load happens in memory. The formulas are short and readable: the second level is just “the products of the selected category”.

🌟 Key Advantages

🔸 One load: the data source is queried once per screen, not on every selection.

🔸 Simple dependent filters: the nested table from GroupBy is already the filtered list.

🔸 No delegation warnings in the dropdowns: collections are in memory and don’t need delegation.

⚠️ Good to Know

🔸 GroupBy and ClearCollect can’t be delegated. The collection only receives as many rows as the data row limit allows: 500 by default, adjustable up to 10,000 in the app settings. For larger tables, load a filtered subset.

🔸 The data in the collection is a snapshot. If other users change products while the screen is open, reload the collection.

🔸 Since Power Apps version 3.24042 column names in GroupBy are written without quotes (Category, not "Category"). Older apps were updated to the new syntax automatically.

🎥 Video Tutorial

🛠️ FAQ

1. Can GroupBy handle large datasets efficiently?

GroupBy itself runs in memory and isn’t delegable. It groups what is in the collection, and the collection is limited by the data row limit (500 by default, maximum 10,000). For large data sources, filter on the server first and group the result.

2. What happens if my grouping field has null or empty values?

Rows with a blank value in the grouping column end up in one group with a blank key. Filter them out before grouping, for example GroupBy(Filter(colProducts, !IsBlank(Category)), Category, CategoryRows).

3. Can I group by multiple fields simultaneously?

Yes. List several columns before the name of the group column: GroupBy(colProducts, Category, SubCategory, CategoryRows) creates one record per combination of category and subcategory.

🔗 Related Tips

🔸 #PowerPlatformTip 81: ‘Fast DataSource Updates’ - more techniques for loading and updating data efficiently once it’s in a collection.

🔸 #PowerPlatformTip 69: ‘Optimize Loading with Fullscreen Reference’ - another straightforward performance win for app startup.