Power Apps Collection: Create One With Collect, ClearCollect and a SharePoint List

Our service desk team wanted a small Power Apps screen that holds data in memory: a quick cart of items an agent adds before saving, plus trimmed copies of the SharePoint request list they can sort and count without waiting on the server every time. That is exactly what a Power Apps collection does.

Here’s what we’re building. Three buttons fill and empty a cart collection, one loads only the New requests from SharePoint, and one copies three columns of the whole list. The labels show the real row counts from my test:

In this tutorial, we’ll create a Power Apps collection from scratch with Collect, remove rows with Remove and Clear, check it in the Variables pane, and build collections from a SharePoint list with a filter and with selected columns. Then we’ll hit the 500-row wall and load all the rows anyway.

A collection is a table that lives inside your app while it runs. You create it with a formula, nothing gets saved to SharePoint, and it’s gone when the app closes. The finished screen has these parts:

• colCart, a hand-built collection with Item, Qty and AddedOn columns, plus Collect, Remove and Clear buttons.
• colNewRequests, the list’s New requests copied with ClearCollect and Filter.
• colSlim, the whole list trimmed to Title, Status and DueDate with ShowColumns.
• Labels that count each collection with CountRows, so you see the real numbers.

Before You Start

You need a Power Apps license that covers SharePoint (most Microsoft 365 business plans include it) and read access to the list. If you’re still deciding between a variable and a collection, my global vs context variables and collections guide explains when each one fits.

I built everything in my Service Desk Demo app on the Service Requests list on /sites/SPGuides. It holds 620 sample requests. These are the columns we’ll use:

Name	Type	Notes
Title	Single line of text	Laptop replacement #1000, Password reset #1005
Status	Choice	New, In Progress, Closed
DueDate	Date only	1/5/2026, 1/10/2026, …
ID	Number (built in)	1 to 620

Build the Demo App Step by Step

New to canvas apps? These steps create a blank app and connect the list. Skip to Step 1 if your app is already connected. More starter builds are on our Power Apps tutorials hub.

1. Go to make.powerapps.com, select Home, then Start with design.

2. On the Create page, pick Create from blank (a canvas app).

3. In Start with a blank canvas, choose Tablet so the buttons and galleries fit side by side.

4. In Power Apps Studio, select Add data, type SharePoint and pick the SharePoint connector. A data source is a connection your app reads from and writes to.

5. Paste your site URL under Connect to a SharePoint site and select Connect.

6. Check Service Requests under Choose a list and select Connect. Press Ctrl+S now and then to save the app.

Two moves you’ll use in every step: the property dropdown left of the fx formula bar picks which property you’re editing, and the Play button at the top right (or holding Alt while you click a button in the editor) runs the formula.

Step 1: Create the Collection With Collect

Goal: a button that adds one cart row each time it’s clicked. You never declare a collection first; Collect creates it on the first run and adds any new columns it sees.

1. Select Insert > Button and set its Text to Collect a row.
2. With the button selected, pick OnSelect in the property dropdown.
3. Type the formula below in the formula bar.

Collect(
    colCart,
    {
        Item: "Laptop dock",
        Qty: 2,
        AddedOn: Date(
            2026,
            10,
            30
        )
    }
)

To see the rows, insert a Vertical gallery, set its Items to colCart, and set the title label’s Text to the first formula below. Add a label with the second formula for the count. Our guide to showing a Power Apps collection in a gallery covers layouts in more detail.

ThisItem.Item & "  x" & ThisItem.Qty & "  |  " & Text(ThisItem.AddedOn, "mm/dd/yyyy")

"colCart rows = " & CountRows(colCart)

Checkpoint: after three clicks, the gallery shows three Laptop dock x2 | 10/30/2026 rows and the label reads colCart rows = 3, as in the finished screen above.

Step 2: Remove One Row or Clear Them All

Add two more buttons. Remove first row deletes one record, and Clear empties the collection but keeps its columns. The Power Apps Remove function guide has more ways to pick which record goes.

Remove(colCart, First(colCart))

Clear(colCart)

In a gallery, you’d usually put Remove(colCart, ThisItem) on a trash icon inside each row instead. Here are more ways to remove items from a Power Apps collection.

Checkpoint: one click on Remove first row drops the count from 3 to 2. Clear takes it to 0.

Step 3: Look Inside the Collection (Variables Pane)

You don’t need a gallery to check what a collection holds. Power Apps Studio lists every collection with its row count.

1. Select the Variables icon (x) on the left rail.
2. Expand Collections. Each one shows (Table: n rows).
3. Hover colCart, select …, then View Table.

The pane shows the current state of the app, so run your buttons first. Older posts tell you to use File > Collections; current Studio puts it in the Variables pane.

Step 4: Create a Collection From a SharePoint List With a Filter

Now the SharePoint part. Same Service Requests list, but we only want the New requests. Put a Filter inside ClearCollect so SharePoint does the filtering (it handles a Status comparison server-side, which is called delegation; see the Power Apps delegation guide for what SharePoint can and can’t filter).

1. Insert a button and set its Text to ClearCollect New requests.
2. Set its OnSelect to the formula below.
3. Add a label with "colNewRequests rows = " & CountRows(colNewRequests).

ClearCollect(
    colNewRequests,
    Filter('Service Requests', Status.Value = "New")
)

ClearCollect clears the collection and then collects, so a second click doesn’t double the rows. The plain Collect(colNewRequests, ...) would append the same 210 rows again.

Checkpoint: the label reads colNewRequests rows = 210. That’s under 500, so it’s every New request in the list.

Step 5: Select Only the Columns You Need (ShowColumns)

A SharePoint list brings a lot of system columns along. ShowColumns keeps only the ones you name. Column names are plain identifiers now, with no quotes; the old "Title" syntax was replaced in Power Apps 3.24042. To add a calculated column instead, use the Power Apps AddColumns function.

ClearCollect(colSlim, ShowColumns('Service Requests', Title, Status, DueDate))

Look at the result: 500 rows, not 620, and Studio showed no delegation warning at all. Open colSlim with View Table and you’ll also see that Status is a record, not text:

That’s why the gallery row uses ThisItem.Status.Value. A choice column always comes back as a record with a Value field.

ThisItem.Title & "  |  " & ThisItem.Status.Value & "  |  due " & Text(ThisItem.DueDate, "mm/dd/yyyy")

Checkpoint: colSlim has only DueDate, Status and Title, and 500 rows. The missing 120 rows are the next section.

Test the App

Press Play and run the buttons in order: Collect a row three times, Remove first row once, then both ClearCollect buttons. Back in the editor, open colCart with View Table:

Here’s what each result tells you. colCart shows 2 rows (3 collected, 1 removed). colNewRequests is 210, the full filtered set. colSlim is 500, which is wrong for a 620-row list. Counting rows in a collection is safe, by the way; CountRows on a SharePoint list is where the delegation warnings start.

Why Your Collection Stops at 500 Rows

Collect and ClearCollect can’t be delegated when the source is a data source. Power Apps pulls only the first batch of rows, and the batch size is the Data row limit under Settings > General. It’s 500 by default. Microsoft Learn now lists 1 to 10,000, but Studio in my tenant still refused anything above 2,000 (“This value needs to be between 1 and 2000”).

ClearCollect(colRequests, 'Service Requests')

With the limit at 500, that formula gave me exactly 500 rows, highest ID 500.

Raising the limit fixes it for this list (at 2,000 I got all 623 rows), but any list bigger than the limit still gets cut, and Microsoft notes that performance can degrade as you raise it, especially for wide tables. Filters with Search behave the same way; see the Power Apps Search function for that case.

Important: nothing warns you here. ShowColumns, Collect and ClearCollect over a whole list return a full-looking table that’s quietly cut off.

Variation: Load More Than 500 Rows in Chunks

The fix is to collect in pieces that each stay under the limit. Every Status value has about 200 requests, so one Collect per status works. The Power Apps ForAll function runs the Collect once for each value:

Clear(colAllRequests);
ForAll(
    [
        "New",
        "In Progress",
        "Closed"
    ] As s,
    Collect(
        colAllRequests,
        Filter(
            'Service Requests',
            Status.Value = s.Value
        )
    )
)

Each filter is delegated to SharePoint, and each chunk is capped separately. That’s the catch: if one status ever passes 500 rows, that chunk gets cut too.

I also tried monthly chunks on DueDate. Compute the dates with With first; putting Date(2026, m.Value, 1) straight inside the Filter gave a delegation warning in my test.

Clear(colByMonth);
ForAll(Sequence(10) As m,
    With({monthStart: Date(2026, m.Value, 1), monthEnd: Date(2026, m.Value + 1, 1)},
        Collect(colByMonth, Filter('Service Requests', DueDate >= monthStart, DueDate < monthEnd))))

Here are all three results side by side:

The status chunks returned 623 rows, highest ID 623, which is the whole list: the 620 sample requests plus 3 test rows I added for another demo. The month chunks returned 620, because those 3 test rows have no DueDate and no month range matches a blank date.

Note: chunk on a column that every row has a value in, then compare your total with the list’s item count.

Troubleshooting

• The collection stops at 500 (or whatever you set): that’s the data row limit. Filter first or load in chunks.
• Rows double every time you click: you used Collect on a load button. Use ClearCollect, or Clear first.
• Status won’t show as text in a label: it’s a choice record. Use ThisItem.Status.Value.
• Old ShowColumns formulas with quoted names: drop the quotes and use the column names as identifiers.
• The collection is empty when the app opens: collections live only while the app runs. Load it in the screen’s OnVisible or the app’s OnStart.
• You need an empty collection with set columns: follow how to create an empty collection in Power Apps.

Where to Take It Next

• Send the cart back to SharePoint in one go: patch a Power Apps collection to a SharePoint list.
• Summarize colAllRequests by Status with the Power Apps GroupBy function.
• Total a numeric column of a collection with the Power Apps Sum function.

If you build apps like this at work, our Power Apps training course covers collections, delegation and SharePoint data step by step.

A few more collection tricks worth bookmarking:

• Add/Update Collection in Power Apps [With Various Examples]
• How to Add Gallery Data to a Collection in Power Apps
• How to Get First Item in Power Apps Collection?
• How to Filter a Collection Based On Another Collection in Power Apps

Frequently Asked Questions