Building OData Filter Expressions for Power Platform and Dynamics 365

OData (Open Data Protocol) is a standard for querying and updating data over HTTP. In the Microsoft Power Platform ecosystem — Dataverse, SharePoint, Dynamics 365 — OData filters are the primary way to retrieve specific records from large datasets. Writing OData filters by hand requires knowing the syntax precisely, and even small mistakes cause cryptic errors.

An OData filter builder lets you construct filter expressions visually and validates them before you use them in a flow, a plugin, or a direct API call.

OData Filter Basics

An OData filter is passed as the $filter query parameter:

GET /api/data/v9.2/accounts?$filter=...

The filter expression uses a SQL-like syntax with these components:

Comparison operators

| Operator | Meaning | Example |

|----------|---------|---------|

| eq | Equal | status eq 'active' |

| ne | Not equal | status ne 'inactive' |

| gt | Greater than | revenue gt 1000000 |

| ge | Greater than or equal | age ge 18 |

| lt | Less than | priority lt 3 |

| le | Less than or equal | score le 100 |

Logical operators

status eq 'active' and revenue gt 1000000
status eq 'prospect' or status eq 'lead'
not (status eq 'closed')

String functions

| Function | Example | Meaning |

|----------|---------|---------|

| startswith | startswith(name,'Contoso') | Name starts with "Contoso" |

| endswith | endswith(email,'@example.com') | Email ends with "@example.com" |

| contains | contains(description,'urgent') | Description contains "urgent" |

| tolower | tolower(name) eq 'acme' | Case-insensitive comparison |

Date functions

createdon ge 2024-01-01T00:00:00Z
year(createdon) eq 2024
month(birthdate) eq 3

Null checks

parentaccountid ne null
emailaddress1 eq null

Common Power Platform OData Patterns

Filter by lookup field (related record)

_primarycontactid_value eq 'a1b2c3d4-e5f6-...'

Lookup fields in Dataverse are stored as GUIDs. Use the _fieldname_value convention.

Filter by option set value

statuscode eq 1

Option set values are integers in OData, not the display label strings.

Filter by date range

createdon ge 2024-01-01T00:00:00Z and createdon lt 2024-04-01T00:00:00Z

Filter using special functions in Dataverse

Microsoft.Dynamics.CRM.Today()
Microsoft.Dynamics.CRM.ThisMonth()
Microsoft.Dynamics.CRM.Last7Days()

These functions are Dataverse-specific and are not part of the OData standard.

Filter accounts owned by current user

_ownerid_value eq {!userID}

In Power Automate, use the triggerOutputs()?['body/InitiationUserId'] dynamic value.

Using the DevHexLab OData Filter Builder

The builder provides a visual interface for constructing filter expressions:

1. Add a condition — select a field, choose an operator, enter a value

2. Combine conditions — connect conditions with AND or OR

3. Group conditions — wrap groups in parentheses to control precedence

4. Preview the filter — see the generated OData filter string

5. Copy to clipboard — paste directly into Power Automate or a Dataverse API call

Common mistakes the builder prevents

String values without quotes (name eq Contoso → name eq 'Contoso')
Using = instead of eq
Missing parentheses in complex AND/OR combinations
Date values in wrong format (must be ISO 8601)

Using OData Filters in Power Automate

In a List rows action (Dataverse) or Get items action (SharePoint):

1. Add the List rows action

2. Click Show advanced options

3. Enter your filter in the Filter rows field

The filter is passed directly to the OData $filter parameter.

Performance Tips

Always filter, never retrieve all rows and filter in the flow. Retrieving 10,000 rows to find 5 matching ones is slow and hits flow limits. Use $filter to retrieve only what you need.

Combine with `$select` to retrieve only the columns you need alongside filtering:

$select=name,revenue,statuscode&$filter=statuscode eq 1

Index the filtered fields. In Dataverse, commonly filtered fields should be indexed for query performance.

Conclusion

OData filters are essential for building efficient Power Platform flows and integrations. Mastering the syntax lets you retrieve exactly the records you need without post-filtering in your flows. The DevHexLab OData Filter Builder generates correct, validated filter expressions that you can use immediately in Power Automate, Power Apps, or direct API calls.