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

1. Use ClearCollect to reset and populate

2. Use Collect to append without clearing

3. Collections store records or tables, not just single values

4. Always handle delegation when connecting to large data sources

5. Use Selected / SelectedItems to get control values

6. Use GroupBy, AddColumns, Ungroup for advanced table manipulation