Power Automate Get items: filter queries and missing rows

Start with four known rows

Get items can succeed while returning fewer rows than your report needs. First, check whether the filter is wrong or the flow is limiting how many rows it returns. Use an approved development list and compare known records rather than trying random expressions in a live flow.

For these examples, use a list of made-up records named Delivery Work. The internal names ExampleKey, Title and WorkState are single-line text; EstimateHours is Number; DueAt is Date and Time. WorkState is deliberately text, not Choice. The four ExampleKey values are our labels, not SharePoint-assigned IDs. Verify actual internal names in your list; renaming a displayed column does not necessarily rename its internal field.

Download the sample JSON data to use as a reference. It is not a ready-to-import SharePoint package. Keep the UTC timestamps when preparing test rows; SharePoint may show them in the viewer's local time zone.

Add a manual trigger and SharePoint Get items, using the test site and list. The following query examples go in its Filter Query field as OData text, without $filter=. They are not Power Fx formulas or Power Automate expression-editor functions. See the connector's Get items parameters.

Example 1: get only ready work

WorkState eq 'Ready'

Expected: W-101 and W-103. W-102 is Draft and W-104 is Done. Use straight single quotes around text values; the field name is unquoted. First inspect the action's returned value array in run history. You should see two objects containing those ExampleKey values.

Add a Compose after an action actually named Get_items to inspect the returned count:

length(body('Get_items')?['value'])

Expected count: 2. This counts the rows returned, not every possible match in the source. Check the action's internal name and returned data before copying the expression. If no rows appear, check the site/list, field type, internal name, connection access and actual values before changing pagination.

Example 2: filter by a text state and a number

WorkState eq 'Ready' and EstimateHours ge 4

Expected: W-101 only. W-103 is Ready but has three estimated hours. The number 4 has no quotes because EstimateHours is numeric. Changing it to '4' changes the value's type; changing and to or changes the question.

Test the exact cutoff: in the development sample, change W-103 from 3 to 4. It should now match ge 4. Then try gt 4; W-103 should no longer match. Restore the sample afterward. Testing the cutoff tells you more than testing only a very large number. Microsoft's SharePoint OData reference describes supported comparisons and string functions.

Example 3: find titles that start with a prefix

startswith(Title, 'Beacon')

Expected: W-101 and W-104. This checks the beginning of Title, so a title such as “Review for Beacon” would not match that prefix. To limit the prefix search to ready work, combine it with the first example:

startswith(Title, 'Beacon') and WorkState eq 'Ready'

Expected: W-101 only. Do not assume an Excel connector's filter syntax, a Dataverse query or a Power Apps StartsWith formula can be pasted into this field unchanged. OData operators are connector-specific. If source values contain apostrophes or come from input, build and test the OData text with the right escaping. Do not remove punctuation from the original data to hide a query error.

Example 4: get one complete UTC calendar day

DueAt ge '2026-09-25T00:00:00Z' and DueAt lt '2026-09-26T00:00:00Z'

Expected: W-101 and W-102. W-101 sits exactly at the inclusive start; W-103 sits exactly at the excluded next-day start. W-104 is one second before the day. Including the start and excluding the next day avoids guessing whether the final time should be 23:59:59 or include fractional seconds.

This example is explicitly a UTC day, not a person's local business day. For a local day, calculate both local midnights and convert each boundary to UTC using the actual time zone. A daylight-saving transition can make that interval 23 or 25 hours. See the worked dates and time zones guide before using a calculated date.

Example 5: check that the report includes every row

Set up a small test in an approved development list to check that all rows are returned. Prepare 120 made-up rows with unique ExampleKey values, all matching the chosen filter. Keep a separate list of every key you expect. The total alone can hide duplicates or missing rows, so compare both the count and the keys.

1. Run Get items with your current settings and capture the action inputs, returned count and keys. Microsoft's Get items walkthrough describes a default of 100 rows. Check your actual settings and run results to see what was returned.
2. For the intended full 120-row report, remove an unintended limiting Top Count, enable Pagination in action Settings where available, and choose a threshold that covers this small test within the flow's allowed limits.
3. Rerun and compare against all 120 expected keys. If you get exactly the configured limit, check the source before assuming you have every row.
4. Place at least one matching test row late in the data and verify that specific key. Test zero matches and a mixed matching/nonmatching set too.

These are checks for you to run. We have not run this sample or flow. For a list over 5,000 rows, pagination and SharePoint's list-view threshold are different limits. Microsoft documents a filtered Get items limitation where later matches may be missed without pagination. You may also need a filter that narrows the results and uses an indexed column to avoid threshold errors. Increasing pagination does not fix every query. See Microsoft's large-list guidance.

Use the limits that apply to your flow's performance profile. The current Power Automate limits reference distinguishes pagination limits by performance profile. Do not copy a maximum from someone else's plan. Each page also uses requests.

Keep filtering, sorting and formatting separate

Get items Filter Query asks the source for matching rows. Filter array can then remove rows from those results, but it cannot recover rows that were never returned. Order By sets the order; Top Count limits the number of results. If you need the latest item, choose the sort order and decide how to handle ties. Do not assume the first returned row is the newest.

A green action means the action completed, not that your business report is complete. Record the filter, internal field names, source permissions, pagination settings, expected key set and edge cases. If the requirement is exactly one record, check zero, one and many matches before using first(). Once you have the right rows, use the Select and Join guide to format a summary and the error-handling guide to plan recovery.

Sources checked September 25, 2026. All data and outputs here are made-up teaching examples. You can check the sample calculations yourself; test how the connector behaves in an approved test flow.