Power Automate: Excel Table Row Filtering Beyond ODATA – Text, Numbers, Dates

The List rows present in a table action in Power Automate offers a built-in Filter Query expecting an OData expression. This works beautifully for text columns, but many makers hit a wall when trying to filter on numeric or date fields – the query either fails silently or returns zero rows. The root cause is that Excel stores numbers and dates as text strings inside the table, and the Excel connector doesn’t interpret them as their native types.

In this article we’ll explore a practical approach: use ODATA for text, and for everything else, rely on Select + Filter Array after type conversion. We’ll walk through a concrete scenario with a project‑task tracker to illustrate each technique.

Scenario: Project Task Tracker

Imagine an Excel table named tblProjects stored in a SharePoint document library. The table contains columns:

• TaskName (Text)
• Priority (Number – 1 to 5)
• DueDate (Date)

Sample data:

The goal is to filter these rows dynamically inside a Power Automate flow.

Filtering Text Columns with ODATA

For text columns you can safely use the native Filter Query parameter. The syntax is standard OData, and the action returns only the matching rows directly.

Equal to (eq)

Find all tasks where TaskName equals exactly “Build prototype”:

TaskName eq 'Build prototype'

Not equal to (ne)

All tasks except “Documentation”:

TaskName ne 'Documentation'

Contains, starts with, ends with

Tasks whose name contains “proto”:

contains(TaskName, 'proto')

Tasks starting with “User”:

startswith(TaskName, 'User')

Tasks ending with “ing”:

endswith(TaskName, 'ing')

Filtering Numeric Columns

If you attempt to use a numeric column directly in the Filter Query (e.g. Priority eq 3), the action will either throw an error or return an empty array. This happens because the underlying value is a string, not a number.

The workaround is a three‑action pattern:

1. List rows – retrieve all rows (or use ODATA text filters to reduce rows first).
2. Select – transform the string column into an actual number using float().
3. Filter Array – apply numeric comparisons on the converted field.

Priority equal to 5

float(item()?['Priority'])?['Value']

item()?['Priority']

Priority greater than or equal to 3

The Filter Array expression for “greater or equals”:

@greaterOrEquals(item()?['Priority'], 3)

Priority between two values

To isolate tasks with priority 2 through 4:

@and(greaterOrEquals(item()?['Priority'], 2), lessOrEquals(item()?['Priority'], 4))

Filtering Date Columns

Date fields suffer from the same limitation – they are stored as text (often an Excel serial number as text). To filter correctly you must:

• Convert the stored date text to an integer (serial number) in a Select action.
• Convert the target filter date to an Excel serial number using a formula inside a Compose action.
• Use Filter Array to compare the two numeric values.

Convert stored date to integer

In the Select action, for each row:

int(item()?['DueDate'])

Convert a filter date to Excel serial number

Suppose you want to filter for tasks due on or after 2026-06-15. Create a Compose action with the following expression (assumes the date is passed as a string or from a trigger):

int(
add(
  div(
    sub(
      ticks(outputs('Compose:UserSelectedDate')),
      ticks('1900-01-01T00:00:00Z')
    ),
    864000000000
  ),
  1
)
)

Replace outputs('Compose:UserSelectedDate') with the actual date source. This formula accounts for the leap‑day bug (Excel serial number 1 = 1900-01-01, but Excel incorrectly treats 1900 as a leap year).

Filter array for dates after or equal

Now use Filter Array to keep rows where the numeric DueDate is greater than or equal to the computed serial number:

@greaterOrEquals(item()?['DueDate'], outputs('Compose:DueDateSerial'))

Performance and Delegation Notes

• OData queries are the most efficient way to reduce row count early. Always try to filter on text columns first before applying the Select + Filter Array pattern for numbers/dates.
• The List rows action has a default page size of 256 and can be increased via the Top Count parameter. If your numeric or date filter must scan the entire table, set a reasonable Top Count to avoid timeouts.
• Filter Array runs entirely in the flow and can be slow on tens of thousands of rows. Pre‑filter with OData whenever possible.
• There is no true delegation to the Excel engine for custom expressions. The connector only supports simple OData comparisons. Our workaround pulls all matching (or all) rows into memory, so keep your data size manageable.

Common Mistakes and Troubleshooting

Recommendation

For robust Excel table filtering in Power Automate:

• Text – use the native Filter Query with OData.
• Numbers and Dates – use Select to convert to a numeric type, then Filter Array for comparisons.
• Keep the flow lean: place text‑based ODATA filters first to reduce the dataset before the more expensive numeric/date operations.

This hybrid approach gives you the best of both worlds – fast server‑side text filtering and reliable client‑side numeric/date filtering.

References

• Original source: How To Filter Excel Table Rows In Power Automate: Text Numbers, Dates by Matthew Devaney
• Microsoft Docs: List rows present in a table (Excel Online Business)
• Microsoft Docs: OData system query options for the Excel connector (placeholder – verify exact URL)
• Date‑to‑Excel serial conversion: Manuel Gomes – Convert Date to Excel Number