#PowerPlatformTip 16: 'PowerApps: Collections Cookbook'

TL;DR: Matthew Devaney’s free Power Apps collections cookbook is a library of copy-and-paste Power Fx recipes. Below are the three recipes I reach for most: row numbers with Sequence() and Index(), a distinct count with Distinct(), and totals per group with GroupBy() and AddColumns().

Numbering rows, counting distinct values, grouping and summing: collection tasks like these go beyond the functions you reach for first. Matthew Devaney’s Power Apps collections cookbook collects ready-to-use recipes for exactly these tasks.

💡 Challenge

You need advanced collection operations that aren’t obvious from the built-in functions, and writing them from scratch each time is slow and error-prone.

✅ Solution

Keep the Power Apps collections cookbook bookmarked and copy the recipe you need. The three recipes below cover the most frequent cases and work on any collection.

🔧 How It’s Done

The examples use this collection:

ClearCollect(colOrders,
    {Customer: "Contoso",  Amount: 120},
    {Customer: "Fabrikam", Amount: 80},
    {Customer: "Contoso",  Amount: 45}
)

1. Add row numbers

🔸 Sequence() produces the numbers 1 to n, Index() picks the matching record, Patch() merges the number into it:

ClearCollect(colNumbered,
    ForAll(
        Sequence(CountRows(colOrders)),
        Patch(Index(colOrders, Value), {RowNo: Value})
    )
)

🔸 Result: every record gets a RowNo column with 1, 2, 3.

2. Count distinct values

🔸 Distinct() returns a one-column table with the column Value:

CountRows(Distinct(colOrders, Customer))

🔸 Result: 2 (Contoso and Fabrikam).

3. Group and sum

🔸 GroupBy() nests the records of each customer in a table column, AddColumns() and Sum() aggregate it, DropColumns() removes the nested table:

ClearCollect(colTotals,
    DropColumns(
        AddColumns(
            GroupBy(colOrders, Customer, Orders),
            Total, Sum(Orders, Amount)
        ),
        Orders
    )
)

🔸 Result: Contoso 165, Fabrikam 80.

4. Find more recipes

🔸 Browse the cookbook for reversing, joining, splitting and transposing collections, paste the recipe into a test screen first, then adapt the column names to your data.

🎉 Result

Row numbers, distinct counts and group totals in a few lines of Power Fx, and a bookmarked source for the less frequent cases.

🌟 Key Advantages

🔸 Ready-to-use snippets for collection tasks that the basic functions do not cover directly.

🔸 No loops with side effects: ForAll() returns a table, so the recipes build new collections instead of patching one record at a time.

🔸 Readable: column names are written without quotes, as in current Power Fx.

🛠️ FAQ

1. Why do the formulas write column names without quotes?

Since Power Apps version 3.24042, GroupBy(), AddColumns() and DropColumns() take column names as identifiers, not as text in double quotes. Older examples on the web still use "Customer"; apps were updated to the new syntax automatically.

2. Do these recipes work on SharePoint or Dataverse tables directly?

They work on any table, but functions like GroupBy() and Distinct() with ForAll() are not always delegable. On large data sources, collect the filtered rows first, then apply the recipe to the collection.

3. Can I modify the examples for my use case?

Yes. The recipes are starting points. Replace the collection and column names and keep the structure.

🔗 Related Tips

• #PowerPlatformTip 93: Add RowNumbers, the row-number recipe in detail.