A Deep Dive into Power Automate Excel Filter Queries: Filtering Greater Than, Less Than, and Removing Blank Rows
I discovered that the “greater than or equal to” (ge) and “less than or equal to” (le) operators cannot be used in Excel filter queries, so I decided to investigate this in detail.
I used filter queries on this Excel file stored in OneDrive to thoroughly test their behavior.
Filtering Strings
A variety of operators are available for filtering string columns:
eq : equals
ne : not equal to
startswith : starts with
endswith : ends with
contains : contains
Both eq and ne can be used as expected.
You can also use startswith and endswith.
The contains operator is also supported, making string filtering quite versatile.
ColumnName eq 'TargetString' // equals
ColumnName ne 'TargetString' // does not equal
startswith(ColumnName, 'TargetString') // starts with
endswith(ColumnName, 'TargetString') // ends with
contains(ColumnName, 'TargetString') // contains
Removing Blank Rows
If you want to remove rows where a certain column is blank, you can achieve this by combining the ne (not equal) operator with an empty string.
ColumnName ne '' // Do not forget to use "''"
Filtering Numbers and Dates/DateTimes
For numbers and dates, you can filter using eq (equals) and ne (not equals) without any issues.
However, Excel filter queries do not support “greater than or equal to” or “less than or equal to” (ge, le). If you try to use these operators, an error occurs: 【Invalid filter clause: unsupported operation. Only single ‘eq’, ‘ne’, ‘contains’, ‘startswith’ or ‘endswith’ is currently supported.】
So, if you want to perform these types of filters, you need to use an array filter after retrieving the data (for dates, it’s easier to work with the ISO 8601 format).
For the left side of the condition, use the column value retrieved from Excel (item()[‘ColumnName’]), and for dates, align the format using formatDateTime.
On the right side, specify the comparison value (number or date).
This enables the desired filtering.
*Note: When filtering by datetime, make sure to consider time zones.
When You Want to Filter Large Amounts of Data Quickly
However, the above methods only filter the data after it has been retrieved, so you need to be aware of Excel’s maximum row retrieval limit of 256 rows.
If you want to filter large amounts of data quickly, it is recommended to use Office Scripts.
