← Al Davies, 250 High Street

Power Automate Get items stops at 100 rows: how to get them all

Get items returns 100 rows by default, so a list with 800 rows gives you 100 and the flow still shows green. To get more, set Top Count in Advanced options (up to 5,000) and turn on Pagination in the action settings, and filter at the source so you fetch only the rows you need. Check the run history to prove the count.

What happens

You build a flow that reads a SharePoint list. It loops over the rows and sends a reminder, adds a total or copies data. It works in testing with 20 rows. Months later the list has 300 rows and some people never get the reminder. Nobody sees an error.

Microsoft's guide to Get items says the default item limit is 100. If you only give the site address and the list name, Power Automate returns 100 items. A Power Platform Community answer says the same: Get items returns 100 items per call by default, and Pagination controls how many rows come back.

So the flow is not broken. It does exactly what it was told. It just was not told to get more.

Signs this is happening to you

Three settings that decide how many rows you get

  1. Filter Query. Asks SharePoint to send only matching rows. This is the best first fix because a smaller answer is faster and cheaper to loop over.
  2. Top Count. In the Get items action, open Advanced options. Top Count sets the number of items to return. Microsoft says you can raise it up to the list view threshold of 5,000. If you go past 5,000, Microsoft says the flow fails with an error.
  3. Pagination. Open the action's Settings and turn it on. Microsoft's guide says to turn it on for lists with more than 5,000 items. A Power Platform Community answer says Pagination must be on to retrieve all matching rows, and that the threshold you set is the most rows returned. Exact names and screens may differ in your tenant.

Pagination has its own cap. Microsoft's Power Automate limits page lists "Paginated items" as 5,000 for the Low performance profile and 100,000 for all others. To process more, Microsoft says to trigger multiple flow runs over your data.

Step 1. Count what you really need

Before you change settings, ask what the flow needs. A reminder flow does not need every row. It needs the open rows that are due soon. One way to start: write the rule in one line, such as "open items due in the next 3 days".

Step 2. Filter at the source

In Get items, open Advanced options and fill in Filter Query. This uses OData. Microsoft gives these examples:

Use the column's internal name, not the name on screen. Microsoft notes that spaces in a column name become _x0020_ in the query. For example, a column shown as "Start Date" is written Start_x0020_Date. Text values go inside single quotes.

Say your list has a Status Choice column with the choices 1 Open, 2 In progress and 3 Done. The number prefix keeps your order. Our advice, not a Microsoft rule: a Choice column sorts by its text. To get only open rows, the Filter Query is:
Status eq '1 Open'

Microsoft lists the operators it supports: lt, le, gt, ge, eq and ne for numbers, and startswith, substringof, eq and ne for text. You can also use Order By, such as Title asc.

Filtering at the source is better than getting all rows and then using a Condition inside a loop. The loop has to touch every row you fetched.

Step 3. Set Top Count

Open Advanced options and type a number in Top Count. Pick a number a little above the most rows you expect. If you expect up to 400 open items, a Top Count of 500 is a reasonable start. This is our advice, not a Microsoft rule.

Step 4. Turn on Pagination if you need more than one page

  1. Select the three dots on the Get items action, then Settings.
  2. Turn Pagination on.
  3. Set the Threshold to the most rows you want. Make it at least as big as the number you expect.
  4. Save.

Microsoft documents a known case: on lists with more than 5,000 items, a filter query can return no records if no matching item is in the first 5,000 items. Microsoft's fix is to turn on Pagination. The Power Platform Community answer also advises indexing the columns you filter on, since the list view threshold can cause problems on big lists. For how indexes work, see "SharePoint list over 5,000 items: what breaks and the fix".

Step 5. Prove it

  1. Count the rows in the list, with the same filter you used in the flow.
  2. Run the flow.
  3. Open the run in the run history and open the Get items step.
  4. Compare the number of items in the output with your count.

If they match, you are done. If the output is exactly 100, 500 or 5,000, you hit a setting. Check Top Count and the Pagination threshold again.

Worked example: an 800-row list

Say a list has 800 rows and 300 of them have Status 1 Open. The flow loops over the result and sends a reminder.

  1. Run it with defaults. Open the run history and open the Get items step. The output holds 100 items, so 700 rows are never checked.
  2. In Advanced options, set Filter Query to Status eq '1 Open' and Top Count to 500.
  3. Turn Pagination on in Settings and set the Threshold to 500.
  4. Run it again. The output now holds 300 items, the same as the filtered count in the list. If you skip the filter and set Top Count and the Threshold to 800, the output holds 800.

On screen, you should see the item count in the Get items output change from 100 to your real count. This example is made up to show the steps.

Troubleshooting Top Count and Filter Query

Make the flow tell you when it hits a limit

One way to start: add a Condition after Get items that checks whether the number of items equals your Top Count. If it does, send yourself a message. A result that sits exactly at the limit is a sign that more rows may exist. This is our advice, not a Microsoft rule.

For a full late-task alert that uses these settings, see "Late tasks nobody notices".

What to avoid

Honest limits

Pagination is a fix for the number of rows, not for speed. A Power Platform Community answer reports that a large pagination limit made a run take about 2 minutes in their test. Fetching fewer rows with a good filter is faster. Also, Microsoft's page on limits says retries and requests from pagination count as action runs, so large pulls use more of your action allowance. Your results will differ by list size and plan. Check with your admin about your plan's limits.

When to get help

You can build all of this yourself in Microsoft 365. If you would rather have it built and tested for your team, I do that. A tracker like this is a fixed price: $5,000 for one, built in 2 weeks. See "What a SharePoint tracker costs to build". Bring one example of the problem to a free call.

Tell me what's still manual

I am Al Davies. I run 250 High Street LLC and build SharePoint trackers and Power Automate flows for operations teams. I work remotely. The first call is free, and I will not try to sell you anything.

See trackers I built: Tools I built

FAQ

Why does Get items return 100 rows?

Microsoft says the default item limit is 100. If you only enter the site address and list name, you get 100 items. Set Top Count, add a filter and turn on Pagination to change that.

Why is Top Count not working, and what is the most rows Get items can return?

Microsoft says Top Count can go up to the list view threshold of 5,000, and that going past it makes the flow fail. With Pagination, Microsoft's limits page lists 5,000 paginated items for the Low performance profile and 100,000 for all others.

Why is my Get items filter query not working or returning nothing?

Microsoft says filters need the internal column name, where a space becomes _x0020_, and that on lists over 5,000 items a filter can return nothing unless you turn on Pagination.

Why is Get items not showing all columns, or showing too many?

Microsoft says Get items retrieves all columns by default; in Advanced options, select Limit columns by view to return only the columns in one view.

Is it better to filter in Get items or in a Condition?

Filter in Get items. SharePoint sends fewer rows, and your flow loops over fewer items.

Sources