How to use Collections in Power Apps

Loading

How to use Collections in Power Apps

Introduction:

Power Apps Collections is a core skill for managing temporary data inside your app. Collections are like in-memory tables that let you store, manipulate, and display data without immediately saving it to a data source.

1️⃣ Creating & Populating Collections

#	Use Case	Formula	Notes
1	Create empty collection	`ClearCollect(colEmpty, {})`	Empty placeholder
2	Collect single record	`Collect(colEmployees, {ID:1, Name:"John"})`	Adds one record
3	Clear and collect	`ClearCollect(colEmployees, EmployeesList)`	Resets before collecting
4	Collect multiple records	`Collect(colEmployees, {ID:1,Name:"John"},{ID:2,Name:"Jane"})`	Adds multiple rows
5	Collect from SharePoint list	`ClearCollect(colSPList, Employees)`	Pulls SharePoint list

2️⃣ Adding / Removing Items

#	Use Case	Formula	Notes
6	Add a record	`Collect(colEmployees, {ID:3,Name:"Mary"})`	Simple add
7	Remove a record by condition	`Remove(colEmployees, LookUp(colEmployees, ID=3))`	Removes matching record
8	Remove multiple	`RemoveIf(colEmployees, Department="HR")`	Delegation-safe removal
9	Update a record	`UpdateIf(colEmployees, ID=1, {Name:"John Smith"})`	Update fields
10	Patch a record	`Patch(colEmployees, LookUp(colEmployees, ID=2), {Name:"Jane Doe"})`	Alternative to UpdateIf

3️⃣ Filtering & Searching Collections

#	Use Case	Formula	Notes
11	Filter by column	`Filter(colEmployees, Department="IT")`	Returns filtered table
12	Search by text	`Filter(colEmployees, TextSearchBox1.Text in Name)`	Partial search
13	Search with case-insensitive	`Filter(colEmployees, TextSearchBox1.Text in Lower(Name))`	Lowercase comparison
14	Top N items	`FirstN(colEmployees, 5)`	First 5 items
15	Last N items	`LastN(colEmployees, 3)`	Last 3 items

4️⃣ Sorting Collections

#	Use Case	Formula	Notes
16	Sort ascending	`Sort(colEmployees, Name, Ascending)`	Alphabetical
17	Sort descending	`Sort(colEmployees, Name, Descending)`	Reverse order
18	Sort by multiple columns	`SortByColumns(colEmployees, "Department","Ascending","Name","Descending")`	Multi-level sort

5️⃣ Combining / Joining Collections

#	Use Case	Formula	Notes
19	Append collections	`Collect(colAll, colSPList)`	Combine two collections
20	Join with AddColumns	`AddColumns(colEmployees, "FullName", Name & " - " & Department)`	Add computed column
21	Merge unique values	`ClearCollect(colMerged, Distinct(col1, Name))`	Keep unique names
22	Lookup join	`AddColumns(colEmployees, "ManagerName", LookUp(colManagers, ID=ManagerID).Name)`	Simple join
23	Concatenate values	`Concat(colEmployees, Name & ", ")`	Returns text string

6️⃣ Grouping & Aggregating

#	Use Case	Formula	Notes
24	Group by column	`GroupBy(colEmployees, "Department", "DeptGroup")`	Creates sub-table per group
25	Count items in group	`AddColumns(GroupBy(colEmployees,"Department","DeptGroup"),"Count",CountRows(DeptGroup))`	Counts per department
26	Sum column in group	`AddColumns(GroupBy(colSales,"Region","RegionSales"),"TotalSales",Sum(RegionSales,Amount))`	Aggregation
27	Average value	`AddColumns(GroupBy(colSales,"Region","RegionSales"),"AvgSales",Average(RegionSales,Amount))`	Computes average

7️⃣ Handling Large Data Sources

#	Use Case	Formula	Notes
28	Lazy load top 500	`ClearCollect(colTop500, FirstN(Employees,500))`	Useful for large SP lists
29	Combine multiple batches	`Collect(colEmployees, Filter(Employees, ID>500 && ID<=1000))`	Pagination pattern
30	Delegation-safe filter	`Filter(Employees, StartsWith(Title,"A"))`	Works for SharePoint / Dataverse

8️⃣ Collections for UI / Controls

#	Use Case	Formula	Notes
31	Populate ComboBox	`ClearCollect(colDepartments, Distinct(Employees, Department))`	Bind to ComboBox.Items
32	DefaultSelectedItems for ComboBox	`Filter(colDepartments, Result="HR")`	Correct for large sources
33	Gallery Items	`Filter(colEmployees, Department=cmbDepartment.Selected.Result)`	Dynamic gallery
34	Checkbox list	`ClearCollect(colSkills, ["Power Apps","Power Automate","Power BI"])`	Bind to checkboxes
35	Toggle collection	`If(chkPowerApps.Value, Collect(colSelectedSkills,"Power Apps"), Remove(colSelectedSkills, "Power Apps"))`	Dynamic selection

9️⃣ Advanced Collection Operations

#	Use Case	Formula	Notes
36	Remove duplicates	`ClearCollect(colUnique, Distinct(colEmployees, Name))`	Single column dedupe
37	Flatten nested table	`Ungroup(GroupBy(colEmployees,"Department","DeptGroup"),"DeptGroup")`	Flatten back
38	Create running total	`AddColumns(colSales,"RunningTotal", Sum(FirstN(colSales, ThisRecord.RowNumber), Amount))`	Requires RowNumber column
39	Transform column	`AddColumns(colEmployees,"Initial",Left(Name,1))`	Compute new column
40	Sort grouped table	`SortByColumns(GroupBy(colEmployees,"Department","DeptGroup"),"Department",Ascending)`	Sort after grouping

🔟 Miscellaneous / Utility

#	Use Case	Formula	Notes
41	Store selected items from a Gallery	`ClearCollect(colSelectedEmployees, galEmployees.AllItems)`	Save user selections
42	Toggle all selections	`If(chkSelectAll.Value, ClearCollect(colSelectedEmployees, colEmployees), Clear(colSelectedEmployees))`	Bulk select/deselect
43	Clone collection	`ClearCollect(colClone, colEmployees)`	Copy collection
44	Count rows	`CountRows(colEmployees)`	Collection size
45	Check if value exists	`If(!IsBlank(LookUp(colEmployees, Name="John")), "Exists","No")`	Validation
46	Export to CSV (text)	`Concat(colEmployees, Name & "," & Department & Char(10))`	Simple export
47	Filter multi-column	`Filter(colEmployees, Department="HR" && Title="Manager")`	Complex condition
48	Sort by custom column	`SortByColumns(AddColumns(colEmployees,"SortKey",If(Department="HR",1,2)),"SortKey",Ascending)`	Custom order
49	Union two collections	`ClearCollect(colUnion, col1); Collect(colUnion, col2)`	Combine tables
50	Intersect collections	`ClearCollect(colIntersect, Filter(col1, Name in col2.Name))`	Items in both

✅ Tips for Working with Collections

Use ClearCollect to reset and populate
Use Collect to append without clearing
Collections store records or tables, not just single values
Always handle delegation when connecting to large data sources
Use Selected / SelectedItems to get control values
Use GroupBy, AddColumns, Ungroup for advanced table manipulation