Power Automate OData Filter Query 101: Filtering SharePoint Columns for Beginners

When building Power Automate flows with SharePoint, you often need to get list items or files that meet specific conditions – for example, “all completed tasks created after Jan 1”. Instead of retrieving all items and then filtering in the flow (which is slow and inefficient), you can use the OData Filter Query option on the Get items (or Get files (properties only)) action. This filter query allows you to retrieve only the items you need by specifying conditions, leveraging SharePoint’s query engine for faster results [sharepains.comteamdynamix.umich.edu]. In this beginner-friendly guide, we’ll explain what OData filter queries are, why they’re useful, and provide examples for filtering various SharePoint column types. We’ll cover Yes/No, Date, Currency, Lookup, Choice (single and multi-select), Person (single and multi-select), Hyperlink, and Managed Metadata columns. For each, we’ll describe the SharePoint column setup, show the Filter Query syntax to use in Power Automate, and explain common issues (like case sensitivity, internal names, and SharePoint delegation/threshold limits). By the end, you should be comfortable crafting OData filter queries to get exactly the data you want – without unnecessary loops or delays. Let’s get started!

What is an OData Filter Query (and Why Use It)?

OData (Open Data Protocol) is a standard query language for web APIs. In SharePoint’s REST API (which Power Automate uses under the hood), queries can be shaped with OData parameters like $filter to select items matching certain criteria [teamdynamix.umich.eduteamdynamix.umich.edu]. The Filter Query field in Power Automate’s SharePoint actions is essentially the $filter portion of an OData query. By entering a filter query, you tell SharePoint to return only items that meet the conditions, saving your flow from handling extraneous data.

Why is this useful? Two big reasons: performance and simplicity. A well-crafted filter query dramatically reduces the data sent over the network and speeds up your flow [sharepains.com]. For example, pulling 5 items that meet a condition is much faster than pulling 5,000 and then filtering them in an Apply to each loop. It also avoids extra loop logic or filter array steps in your flow – the query does the work for you. This is crucial for large lists: SharePoint has a 5,000 item list view threshold, and filtering server-side helps you stay within that limit by using indexed columns and avoiding non-delegable operations (more on that shortly).

Where can you use it? Primarily in the Get items action for SharePoint lists, under Filter Query in the action’s advanced options [teamdynamix.umich.edu]. The same syntax also works in similar actions like Get files (properties only) for document libraries and any other SharePoint connector action that offers a filter query field – the logic is identical for files vs. list items. (Many other connectors, such as Dataverse, SQL Server, etc., also accept OData filters, though their field names and capabilities differ. In this post we’ll focus on SharePoint.)

Above: The Get items action in Power Automate, with the Filter Query field (advanced options) where you can enter an OData filter string [teamdynamix.umich.edu].

OData Filter Query Basics (Syntax & Tips)

An OData filter query follows a basic pattern:

<InternalFieldName> <operator> <value> [logical operator <second condition> ...]

For example: Status eq 'Completed' or Quantity le 100. Let’s break down the components and best practices:

Internal Field Name – Use the field’s internal name as seen by SharePoint, not the display name. Internal names often differ (e.g. a column labeled "Project Name" might have internal name Project_x0020_Name). You can find the internal name by going to the column settings in SharePoint – the URL will include Field=<Name> [teamdynamix.umich.edu]. Always use that exact name (including any _x0020_ codes for spaces). Using the wrong name is a common mistake that leads to “column not found” errors.
Comparison Operator – OData supports operators like eq (equals), ne (not equals), lt (less than), gt (greater than), le (≤), ge (≥). These must be in lowercase [teamdynamix.umich.edu]. For example, use eq rather than EQ. Logical operators and / or are also lowercase.
Value – The value to compare against. For text or date/time, surround the value in single quotes. For numbers and booleans, no quotes are needed (more on booleans soon). If comparing to an empty/null value, use null (no quotes). Examples: Title eq 'Project A' (Title is text), Amount gt 500 (Amount is numeric), DueDate eq null (DueDate blank). For date/time values, use ISO 8601 format (YYYY-MM-DDTHH:MM:SSZ) and prefix with the keyword datetime – e.g. Created ge datetime'2023-01-01T00:00:00Z' [sharepoint.stackexchange.com].
Combining Multiple Conditions – You can chain conditions with and / or. Use parentheses to group conditions when mixing and/or to avoid ambiguity. For example: (Status eq 'Active' and Priority eq 'High') or (Status eq 'Active' and DueDate lt datetime'2023-12-31T23:59:00Z'). It’s a good practice to put each clause in parentheses, especially if using or, to ensure SharePoint evaluates it as intended [sharepains.com]. If all you have are and conditions, parentheses are optional but can improve readability.
Function Operations – OData provides functions for partial matches and date parts. Two commonly used string functions are:
- startswith(<Field>, '<value>') – checks if the field value begins with a substring. E.g. startswith(Title, 'Project') is true for "Project Alpha" and "Project123", but not "New Project".
- substringof('<value>', <Field>) – checks if the field value contains a substring anywhere [teamdynamix.umich.edu]. Note: the substring goes first, the field second (this trips people up). E.g. substringof('Project', Title) is true for "New Project Plan" (because Title contains "Project").
  Both functions are case-sensitive in SharePoint OData. If casing is an issue, you might need to ensure consistent casing in your data or use a workaround (SharePoint’s REST API doesn’t support a tolower() function in filters). There are also date functions like year(), month(), day() that can extract parts of dates for filtering (e.g. year(Created) eq 2025), but such functions can prevent the query from using indexes – use sparingly.
Case Sensitivity – In OData syntax, the field names and operators are case-sensitive (as noted, use exact internal name and lowercase operators). The string values in comparisons are usually case-insensitive for SharePoint text columns (SharePoint filtering of text is not case-sensitive by default in most cases). For example, Title eq 'Project A' should match "project a" in a Title field. However, functions like substringof and startswith are case-sensitive for the substring. It’s safest to match the exact case or use consistent casing when storing data.
Flow Expression Injection – You can dynamically build filter queries using flow expressions. For instance, to filter by the current date or a value from a previous step, you might use an expression inside the quotes. Example: StartDate ge '@{utcNow()}'. Be mindful of the quotes – when the flow runs, the expression will be replaced with a value. The resulting string still needs to be a valid OData query. Often you’ll see date expressions combined with the datetime'...' wrapper, like: Created ge datetime'@{formatDateTime(utcNow(),'yyyy-MM-ddTHH:00:00Z')}'. Keep the syntax exact (the entire datetime literal in single quotes).

Delegation and List Thresholds: A big “gotcha” when filtering SharePoint data is the list view threshold (5000 items). SharePoint can only efficiently filter by indexed columns beyond 5000 items. If your filter uses a column that isn’t indexed and the list is very large, the query may fail or time out. To avoid this, index the columns you plan to filter on (you can set this in List Settings > Indexing). Yes/No, Number, Date, Single-choice, and Lookup (single-value) columns can be indexed. Multi-value fields (like multi-choice, multi-person, multi-lookup, managed metadata) cannot be indexed, so filtering on those in large lists will not work once the threshold is exceeded [teamdynamix.umich.edu]. In such cases, you might need to rethink your approach (filter on another indexed field first, or retrieve and post-filter in the flow). The filter queries themselves don’t have a “delegation limit” like in Power Apps, but they rely on SharePoint’s ability to handle the query, which is where indexing and supported operations matter.

With the general mechanics covered, let’s dive into examples for each column type. We’ll illustrate how to write the filter query and mention any quirks or pitfalls specific to that field type.

Filtering a Yes/No (Boolean) Column

SharePoint Column Setup: A Yes/No column is a boolean (True/False) field (rendered as a checkbox in SharePoint). Let’s say we have a “Active” column (Yes for active items, No for inactive). Internally, SharePoint stores Yes as true and No as false.

Filter Query (Yes/No Example): To filter items where Active is Yes (true), use:

Active eq 1

To filter where Active is No: Active eq 0.

Why 1 and 0? SharePoint’s OData implementation expects boolean values as numeric 1 (true) or 0 (false) in filter queries [teamdynamix.umich.edu]. If you try Active eq true or Active eq 'Yes', it will not work. The correct approach is to use 1 for “Yes/True” and 0 for “No/False” [teamdynamix.umich.edu].

Notes & Pitfalls:

Use 1/0, not true/false: As noted, this is specific to SharePoint’s OData. Even though the JSON output returns "Active": true or false, the filter query must use Active eq 1 or eq 0. This is a common source of confusion.
Internal name check: If your column is named Active (internal name likely “Active”), that’s straightforward. If it has spaces or special chars, ensure you use the internal name. E.g. a column "Is Completed?" might have internal name IsCompleted or Is_x0020_Completed (check in settings).
Index for large lists: Yes/No columns can be indexed. If you have thousands of items and plan to filter by this field, mark it indexed in the list settings to avoid threshold issues.
Filtering for “No” (false) vs blank: A Yes/No field always has a value (false by default if not set), so eq 0 covers both explicitly unchecked and “not set” (there is no null for Yes/No). If you want items where the box is unchecked, use eq 0. There’s no need for or Is null in this case.

Filtering a Date/Time Column

SharePoint Column Setup: Date or Date & Time columns store dates (with or without time portion). For example, a “DueDate” column (Date Only) or “EventDate” (Date & Time). SharePoint stores dates in UTC behind the scenes.

Filter Query (Date Example): Suppose we want items with DueDate on or after Dec 25, 2025. We would write:

DueDate ge datetime'2025-12-25T00:00:00Z'

This uses the ge (≥) operator and the date 2025-12-25 at midnight UTC. We wrap the date/time in datetime'...' as required by SharePoint OData for date comparisons [sharepoint.stackexchange.com]. If we wanted only those due on that exact date and our field is Date-only (no time), we could check for items between 2025-12-25 00:00 and 2025-12-25 23:59 using a combination (DueDate ge datetime'2025-12-25T00:00:00Z' and DueDate lt datetime'2025-12-26T00:00:00Z'). Often, though, you’ll filter relative dates (e.g. Created in last 30 days) using expressions.

Notes & Pitfalls:

Use datetime'...' format: SharePoint requires the datetime literal around ISO date strings in OData filters [sharepoint.stackexchange.com]. If you omit it (e.g. Created ge '2025-12-25'), the query will error or not behave as expected. Always include the time and Z (UTC) if using datetime in the query.
Time zones: The filter value should be in UTC (the Z at the end denotes Zulu/UTC time). SharePoint stores date/time in UTC. If your column is Date Only, the time is typically treated as 00:00:00 UTC on that date. Be mindful that ge datetime'2025-12-25T00:00:00Z' will include items from the entirety of Dec 25 in your local time if your time zone is behind/ahead UTC (because it’s inclusive from that midnight UTC). For most cases, that’s fine; just remember SharePoint’s comparison is in UTC.
Filtering “today” or dynamic dates: You can use flow expressions to insert dynamic dates. For example, to get items created yesterday, one approach is:
Created ge datetime'@{formatDateTime(addDays(utcNow(), -1), 'yyyy-MM-dd')}T00:00:00Z' and Created lt datetime'@{formatDateTime(utcNow(),'yyyy-MM-dd')}T00:00:00Z'
This looks complex, but essentially plugs in “yesterday 00:00:00” and “today 00:00:00” as the range (so everything from yesterday) [stackoverflow.com]. The key is formatting the date strings exactly and wrapping with datetime'.
Equality vs range: If you try Created eq datetime'2025-12-25T00:00:00Z', it will only match items created exactly at that moment (probably none). To get a specific date, use a range as shown or the day() function (if small lists). For a Date-Only field, you could also compare to the date string without time (some have reported DueDate eq '2025-12-25' working for Date-only fields, but the safest is to use the full datetime format or a range).
Indexing: Date columns are good candidates for indexing if you frequently query by date (e.g. filtering by Created or Modified date).
Delegation in formulas: If building the filter string with expressions, test the output to ensure it’s a valid ISO date. A common error is a mis-formatted date string or forgetting part of the syntax, resulting in an “Invalid filter” error. It helps to compose the date string in a Compose action and inspect it for correctness if you run into issues.

Filtering a Currency Column

SharePoint Column Setup: A Currency column stores numeric values (with a specific currency format, e.g. $ or €). For filtering purposes, it behaves like a Number column (the currency symbol is not stored in the data, just the number and currency locale). Example: “Budget” column storing project budget amounts.

Filter Query (Currency Example): If we want items with Budget over 5,000, we can use:

Budget gt 5000

This will return items where the Budget value is greater than 5000. Similarly, Budget eq 0 would find items with a 0 value, etc.

Notes & Pitfalls:

Treat as number: Even though it’s “Currency”, you do not include currency symbols or formatting in the query – just use the numeric value (no quotes). For example, use 5000, not '$5,000' (that would be a string and would not work).
Decimals: If your currency has cents/decimal, you can use decimals in the query. For example Price eq 19.99 or Price lt 100.5. Use a dot . for decimal separator.
Commas: Don’t use commas in the number (e.g. use 10000 not 10,000). Commas would be interpreted incorrectly or break the query.
Internal name: Watch out if your column name contains a currency symbol or spaces. For instance, a column named “Cost ($)” might have an internal name like Cost_x0020__x0028__x0024__x0029_ (SharePoint encodes special characters). Always verify the internal name. In our Budget example, if the display name is “Budget” then internal is likely also Budget.
Comparison operators: You can use any numeric operator on currency: eq, ne, lt, le, gt, ge. You can also use startswith or substringof on a currency field, but that’s unusual (it would treat the number as a string, e.g. startswith(Budget, '5') finds budgets starting with digit 5, meaning 5 or Fifty-something – probably not very useful!).
Null vs 0: If the currency field is optional and not filled in, those items have no value (null). To find items where Budget is blank, use Budget eq null. That is different from Budget eq 0 (which finds explicitly entered zero values).
Indexing: Currency (being a number column under the hood) can be indexed. For large lists, index it if you plan to query on it frequently.
Locale considerations: The filter query is not locale-aware – you must input the value as an invariant number. So if you’re in a locale that normally uses comma for decimals, still use a dot in the OData query.

Filtering a Lookup Column (Single-Value Lookup)

SharePoint Column Setup: A Lookup column links to an item in another list. For example, a “Category” lookup that points to a Category list. In SharePoint, a single-value lookup stores the ID of the related item (and displays a secondary value, typically the Title of that item). Internally, SharePoint actually exposes two pieces: CategoryId (the foreign key ID) and Category (a navigation reference to the item, which can be expanded to get the linked item’s fields like Title).

Filter Query (Lookup Example): There are two ways to filter a lookup:

By ID (best performance): Use the lookup’s internal name with Id appended. For example, to get items where Category points to the item with ID 3 in the Categories list:
```
CategoryId eq 3
```
This directly filters by the stored lookup ID. It’s simple and efficient (numeric comparison). Use this if you know the ID of the lookup target you want.
By the lookup value (text): Use the navigation property with a subfield, typically Title. For example, to get items whose Category’s Title is "Marketing":
```
Category/Title eq 'Marketing'
```
This instructs SharePoint to filter by the Title of the related item [teamdynamix.umich.edu]. Under the covers, it’s doing an $expand on Category and then filtering. This is convenient if you don’t know the ID but know the name. Make sure to use the internal name of the field you’re comparing – usually Title or Name of the target list’s item. (If the lookup is pointing to a list where the primary column is something else, use that. E.g. a lookup to a document library might use File/Name or to a custom list with a "Name" field you’d use that.)

Notes & Pitfalls:

Internal names and “Id” field: The internal name of your lookup column is whatever you see in the URL on list settings (e.g. Category). To filter by ID, append Id to that name – this is a special convention in SharePoint’s OData. You won’t see “CategoryId” in the list settings, but it exists for filtering. Using CategoryId eq 3 works because SharePoint has a hidden numeric field for the lookup ID [learn.microsoft.com]. Do not put quotes around the number (3 in this case).
Value vs ID tradeoff: Filtering by ID is exact and fast. Filtering by Title is user-friendly but can be problematic if the lookup value is not unique or if values change. If multiple categories have the same Title, Category/Title eq 'Marketing' will return items in any of those categories. If a category name changes, your filter might break or return nothing (since the items’ linked ID stays the same but the Title in the category list changed). So use Title filtering with caution.
Indexing and threshold: Single-value lookup columns can be indexed. If you have a large list and need to filter by a lookup, index the lookup column on the source list. However, note that SharePoint’s list view threshold has a secondary limit: a query can reference at most 8 lookup-type fields in a filter/query (lookup, person, choice, etc. count towards this in certain ways). Simple filters on one lookup are fine, but be wary of very complex queries hitting that limit. For large lists (>5000 items), prefer filtering by ID. SharePoint can utilize the index on the lookup ID field efficiently. Filtering by Category/Title might not be supported once the list is beyond threshold (and it could time out or fail if not indexed) [learn.microsoft.com]. If you suspect issues, try switching to ID filtering.
Null (no selection): To find items where the lookup is blank (no value selected), you can use CategoryId eq null. That will return items with no linked category.
Multiple conditions on lookup fields: If you want to filter on the lookup’s properties beyond just one field, note that you can only directly filter on the one field via the navigation (Title, or perhaps another field if you expanded it). For example, you cannot do Category/SomeOtherField eq 'X' unless the lookup column was configured to show that field (SharePoint only automatically exposes certain key fields). For more advanced lookup queries, you might need to use an HTTP request or filter by the ID then handle other conditions in the flow.
Example: Let’s say Category is a lookup to a Departments list that has Department Name (Title) and maybe a Code. If you want all items in Department with Code "HR", you can’t directly filter Category/Code eq 'HR' unless you set up the lookup to show Code (which SharePoint doesn’t do out of box – it only shows Title by default). A workaround: ensure the Title contains the code or create a calculated/combined field to filter on.
Pitfall – lookup values with special characters: If the Title has quotes or special characters, you need to escape them in the query. e.g. If a Category is Bob's Burgers, you’d write Category/Title eq 'Bob''s Burgers' (double single-quote to escape an apostrophe). This applies to any OData string.

Filtering a Choice Column (Single-Select)

SharePoint Column Setup: A single-select Choice column provides a dropdown of fixed options, but only one can be selected. For example, “Status” with choices Open, In Progress, Closed. In SharePoint, the value is stored as text (one of the allowed values).

Filter Query (Single Choice Example): To get items where Status is “Closed”, you’d write:

Status eq 'Closed'

It’s that straightforward – provide the choice value in quotes. If you need items where Status is not a certain value, use ne (e.g. Status ne 'Closed' for everything not Closed).

Notes & Pitfalls:

Exact match of text: The value in the query has to exactly match the choice text (case and spelling) as it exists in the list. Choice comparisons in SharePoint are case-insensitive (since underlying it’s just text), but to avoid confusion, match the case exactly. If your choice has a trailing space or other hidden character (rare, but can happen if someone added a space when creating the choice), that would matter – so double-check the choice spelling.
Internal name vs display name: Use the internal name of the column. If your choice column is “Project Status” (display name), the internal might be ProjectStatus or Project_x0020_Status. Use that in the filter (ProjectStatus eq 'Open'). If unsure, check the list settings URL for Field. Using the display name with spaces will cause an error.
Choices with special characters: If a choice value contains a comma, semicolon, etc., you do not need to escape those (only quotes need escaping). Just put the whole value in single quotes. Example: if a choice is "On Hold/Blocked", you’d do Status eq 'On Hold/Blocked'. The slash or spaces are fine inside the quotes.
Changing choice values: If someone renames a choice in the list settings, items that had the old value will now show the new value (SharePoint updates them). So your filter query should use the current value. If you filter by a choice that no longer exists in the allowed set, SharePoint will just return no items (or error if the value is really not valid text? Usually it just yields nothing).
Delegation/indexing: Choice columns are not lookup fields per se, and in SharePoint they can be indexed. If you have a large list, it’s a good idea to index important choice columns to enable server-side filtering beyond 5000 items. Filtering by a non-indexed choice on a huge list might hit the threshold (though if the first filter in query is on a non-indexed field, SharePoint will complain; sometimes combining with an indexed filter can offload some of it – but best to index the choice field itself).
Null or empty: A choice column can technically be empty if no default and user hasn’t set it (unless it’s required). To filter items where a choice column is blank, use Status eq null.
Multiple choice values (OR logic): If you need to get items that are either “Open” or “In Progress”, you can use or:
(Status eq 'Open') or (Status eq 'In Progress').
Note each condition in parentheses for clarity.
No wildcard in eq: There’s no wildcard or partial match in an eq query. You can’t do Status eq 'Clos*' or such. You’d need to use a function like startswith or substringof on a text field. For choices specifically, since the set is limited, it’s usually not needed – you know the exact values. If you ever needed to filter where choice contains something (maybe if choices are phrases), you could treat it as text: e.g. substringof('Closed', Status) – but that would also match "Closed - needs review" vs "Closed", etc. Typically, stick with eq/ne for choices.

Filtering a Choice Column (Multi-Select)

SharePoint Column Setup: A multi-select Choice column allows selecting multiple values from a set. SharePoint stores all selected options in one field, separated by ;# in the internal data (in modern API outputs, they may come as an array of values in JSON, but in OData query context it’s treated as a single concatenated string of choices).

Filter Query (Multi-Choice Example): OData does not have an “includes” operator, so filtering multi-select fields is tricky. You cannot directly ask for “contains this option” as a discrete condition. However, a common workaround is to use the substringof function to search the string of values. For example, imagine a multi-choice “Locations” field where an item could have “NY” and “LA” selected. To find items that have “NY” as one of the locations, you can try:

substringof('NY', Locations)

This will return true if the text "NY" appears anywhere in the Locations value string [reddit.comreddit.com]. In practice, that should catch items where "NY" is one of the choices (because SharePoint would store it like "NY;#LA" or "LA;#NY" internally). Another approach some use is an eq against the full string if you know the exact combination, but that is rarely useful unless you expect one specific combination of selections.

Notes & Pitfalls:

No native “includes” support: According to Microsoft, multi-valued fields can’t be directly queried by OData filter (there’s no equivalent to the CAML <Contains> for multivalue) [learn.microsoft.com]. The substringof hack is essentially treating the field as one long text. It works in many simple cases but it’s not officially guaranteed for every scenario.
Case sensitivity: substringof is case-sensitive. Ensure you match the case of the choice value exactly as stored.
False positives: Be careful – substringof('NY', Locations) will also match "ANY" or "Sydney" if those were part of the string. To mitigate, you might include the delimiter. E.g., if "NY" is a standalone choice, the stored string would have "NY;#" around it (if other values follow). You could search for 'NY;#' as a substring to be more specific. But if "NY" could be the last value, it might not have ;# after it. This is why this approach is a bit unreliable if choice values are substrings of each other or of other words. If your choices are distinctly different words or abbreviations, it’s usually fine.
Performance and delegation: This approach will likely not be delegable on large lists. SharePoint can’t use an index with substringof because it has to scan each item’s values. If your list is over 5000 items, a substring filter might just fail or return a partial set. There’s no official documentation on the cutoff, but treat it as non-indexed text filtering.
Alternative approaches: If you consistently need to query multi-choice fields and performance is an issue, consider maintaining a separate hidden text field (perhaps with a flow or SharePoint workflow) that concatenates the choices (e.g. "|NY|LA|"), then filter on that with a simple contains logic. Or break out the choices into multiple boolean columns if feasible (one column per key choice that’s true/false). These are design trade-offs – often we just filter after retrieving.
Filtering in flow instead: If you can’t get the OData filter to do what you want for multi-choice, you might retrieve items (maybe filtered by some other criteria if possible to narrow it) and then use the Filter array action in Power Automate to check if the value exists in the array of choices. This is an afterthought because it happens in memory in the flow, but it may be necessary for complex scenarios.
Example scenario: Suppose a list of events with a multi-select “Audience” choice (options like Youth, Adult, Senior). To find events for “Youth”, substringof('Youth', Audience) might work. But if one option was “Youth Sports” and another “Youth Education”, and you wanted specifically those with “Youth Sports”, substringof would also catch “Youth Education”. There’s unfortunately no straightforward way to do an exact contains for one multi-choice option via OData. You’d have to rely on the uniqueness of the strings or combine conditions to exclude undesired matches (which can get messy).
Testing multi-choice queries: It’s a good practice to test your filter on a small sample list or in the browser via the REST API to ensure it returns what you expect. Multi-choice filtering is not as foolproof as single-value filtering.

Filtering a Person (People Picker) Column – Single Person

SharePoint Column Setup: A Person or Group column (single-select) references a user (or SharePoint group). It’s essentially a special lookup to the hidden User Information list. In a list item, a Person field has an associated User ID (the SharePoint ID of that user in the site collection) and is often expanded to display the person’s Display Name (which appears in the column), plus other details like email, job title, etc., if requested.

For example, “AssignedTo” might be a single-person column for a Task’s assignee.

Filter Query (Person Example): The most reliable way to filter by a person field is to use one of the user’s properties. Commonly used: Email or Display Name. Email is usually unique and consistent, so let’s use that. To find tasks assigned to the user with email john.doe@company.com:

AssignedTo/EMail eq 'john.doe@company.com'

Here AssignedTo is the person field’s internal name, /EMail accesses the user’s email property, and we compare to the email string [tomriha.com]. Note: EMail has a capital “M” – that is the exact internal name of the email field in SharePoint’s user reference (it’s not a typo). Alternatively, we could filter by the display name (Title property). For example, AssignedTo/Title eq 'John Doe' (or 'Doe, John' depending on how names are displayed in your tenant) [teamdynamix.umich.edu]. However, display names can be non-unique (two John Smiths) and can change (if someone updates their profile to add middle initial, etc.), so email tends to be safer.

You can also filter by the user’s SharePoint ID using the pattern from lookup: AssignedToId eq 23 (if you know user 23 is John Doe). But User IDs vary by site and aren’t easy to obtain directly in flow without an extra lookup, so we usually stick to email or name.

Notes & Pitfalls:

Use a subfield (Email, Title, etc.): You cannot just do AssignedTo eq 'John Doe' or AssignedTo eq 'john.doe@company.com'. The person field is complex; you must specify which property to filter on. The most common are AssignedTo/EMail and AssignedTo/Title. Another property is AssignedTo/Id (the user’s ID in the User Info list), but if you have that ID, you might as well use AssignedToId eq ... as noted. There’s also AssignedTo/Department, AssignedTo/JobTitle etc., if the person column is configured to bring those in (or even if not, SharePoint’s REST might still allow querying them if the profile has them) [teamdynamix.umich.edu]. For instance, if you wanted all items where AssignedTo’s department is "Finance", you could do AssignedTo/Department eq 'Finance' [teamdynamix.umich.edu]. This will only work if the Department field is populated in Azure AD/M365 profile for those users. Keep in mind, not all user profile properties are accessible via this route, but commonly Department, JobTitle, etc., are.
Internal name of person field: If the field is "Assigned To" (with a space), internal might be AssignedTo or Assigned_x0020_To. Check the actual internal name. In many cases, SharePoint will strip spaces (AssignedTo).
Formatting the email string: Use the exact email address string. It’s case-insensitive (email addresses aren’t case sensitive). To be safe, use the same casing as in Azure AD (often all lower-case).
Display Name format: If using /Title, remember SharePoint person Title is the full name as displayed in the site. Many organizations use "First Last", but some (like in the example from Michigan Medicine) use "Last, First" [teamdynamix.umich.edu]. Check how it appears in your list. You must match it exactly. E.g., AssignedTo/Title eq 'Doe, John' including the comma if that’s how it shows. This is another reason email is easier.
Multiple person fields with same name: If your list has multiple person fields (like RequestedBy, ApprovedBy), be careful to use the correct one in the filter and not to confuse their values.
Current user filtering: A common need is “items assigned to the current user”. You might think of using a placeholder, but OData in SharePoint doesn’t support a direct current user token in filter [learn.microsoft.com]. The typical pattern is to use the user’s email from the flow context. For example, in a flow you could do: AssignedTo/EMail eq '@{user().email}' if running in a user context, or get the user’s email from a trigger or another action.
Indexing: Person columns count as lookup columns. You can index a single-value person column, which is recommended if you filter on it and have lots of items. This helps avoid the 5000-item threshold issue.
Delegation limits: As with lookups, queries on person fields might fail on large lists if not indexed. Also, if you query by a property like Department which isn’t indexed (the person field only indexes the user ID perhaps), SharePoint might struggle if the list is huge. But typically filtering by AssignedTo/EMail uses the underlying user ID index effectively (not guaranteed, but likely).
SharePoint groups: If the column allows SharePoint groups or if someone picks a group as the value, note that AssignedTo/Title would be the group name, and AssignedTo/EMail might be blank (groups don’t have an email). So if your person field is People and Groups, filtering by email might not catch group assignments. In those cases, filtering by Title (group name) could work. Example: Approver/Title eq 'Marketing Team Members' (name of a SharePoint group).
Pitfall – special characters in names: If someone’s display name contains a single quote, e.g., O'Connor, Jane, you’d need to escape it as O''Connor, Jane in the query string (similar to the earlier example for lookup/choice).
Advanced – filtering by multiple persons: If you want items assigned to either Alice or Bob, you can do: (AssignedTo/EMail eq 'alice@co.com') or (AssignedTo/EMail eq 'bob@co.com').
Example uses:
- Find item by exact person: as above.
- Find item where person’s property matches something else: e.g. tasks where AssignedTo’s Job Title is "Manager": AssignedTo/JobTitle eq 'Manager' (this assumes user profiles have Job Title field).
- These advanced queries are powerful but remember, they rely on data in the user profile at the time of query.

Filtering a Person Column – Multiple Selections

SharePoint Column Setup: A Person/Group column can be configured to allow multiple people. In SharePoint, multiple persons are stored similarly to multi-lookups: essentially an array of user entities (or a string of multiple names/IDs in older format). For example, an “Approvers” field where you can select several people.

Filter Query (Multi-Person Example): Filtering multi-person is challenging for the same reasons as multi-choice and multi-lookup. There’s no direct OData way to say “include this user”. However, SharePoint’s OData does allow a similar trick as lookup: you can try using the /EMail or /Title on the person field, which may evaluate true if any of the people match. For example, to find items where John Doe is one of the Approvers:

Approvers/EMail eq 'john.doe@company.com'

In many cases, this works – SharePoint will return the item if any of the Approvers has that email [community.powerplatform.comteamdynamix.umich.edu]. You could also try Approvers/Title eq 'John Doe' (with the same caveats about display name format as above).

Behind the scenes, it’s not well documented how this query is processed for multi-valued fields. The official documentation says multi-value lookups (which multi-person essentially is) cannot be directly queried [learn.microsoft.com]. But practically, many have found that using the /EMail or /Title syntax returns the desired items if the list is under threshold. It likely performs a scan and checks each person.

Notes & Pitfalls:

Not officially supported on large lists: The /EMail eq 'x' filter on a multi-person column will not use an index (you cannot index multi-person columns). If the list is large, SharePoint might refuse to run the query (it might give an error like “The query cannot be completed because it includes lookup columns and the total exceeds the threshold”). In smaller lists it should work fine; in very large lists it may fail or only return partial data without warning. Treat this as a convenience for small/medium lists, but not a robust solution at scale [teamdynamix.umich.edu].
Group membership not resolved: If a SharePoint group is in the multi-person field and you filter by a user’s email, that will not return items where the user is inside that group. OData filter doesn’t expand group membership. It only matches the user accounts directly in the field. Keep that in mind if your column allows groups.
Multiple matches: If the field has multiple people and more than one match your query (say you query by Department: Approvers/Department eq 'Finance' and an item has two people from Finance), that item still only appears once in results (it doesn’t duplicate).
Alternative approach for multi-person: Similar to multi-choice, if you often need to query “does this item include person X?”, an alternative is to maintain a hidden text field that concatenates all emails or IDs, and filter on that with substring. For example, a flow could maintain ApproversEmails = "john.doe@company.com;jane.smith@company.com". Then filter: substringof('john.doe@company.com', ApproversEmails). This is a workaround to simulate an “includes” search. It has the usual caveats of substring (case, partial matches).
Filter in flow approach: If OData filtering isn’t viable (say the list is too large or you need an exact match on one person in the multi), you might retrieve all items filtered by something else (or all if necessary, though that can be heavy) and then use a Filter array to keep items where the person array contains the target user. In Power Automate, the expression could be something like:
@contains([2,4,6,8], item('ID'))
(This example conceptually checks the array of ID's and includes if the ID exists.

CAML query via HTTP: As an advanced solution, you can always revert to SharePoint CAML query (via the SharePoint REST API call in an HTTP action) to leverage an <Includes> condition for multi-value fields. That’s beyond the scope of this OData-focused article, but it’s mentioned as a possible route if absolutely needed [teamdynamix.umich.edu].
Pitfall – multiple criteria on multi-person: If you try something like Approvers/Title eq 'John Doe' and Approvers/Title eq 'Jane Smith', that will likely never return anything – a single iteration of the field can’t be two values at once. If you want items where both John and Jane are in the Approvers, you have to use a different approach (OData filter can’t handle that logic directly on multi, since it doesn’t support an “AND includes” for multivalue). You’d need to fetch items with John, then filter those for Jane in a second step, for instance.
Summary: Use Field/EMail eq 'user@domain' for a quick filter on multi-person fields, but be cautious with scale. Always test with known items to ensure the query behaves as expected.

Filtering a Hyperlink (URL) Column

SharePoint Column Setup: A Hyperlink or Picture column in SharePoint consists of two parts: the URL and a Description. In list views, SharePoint might display the description as the hyperlink text (if a description is provided) or the URL itself if no description. Internally, the field is of type SP.FieldUrlValue, which has sub-properties Url and Description.

Filter Query (Hyperlink Example): Unfortunately, OData filtering on hyperlink fields is not supported [sharepoint.stackexchange.com]. There is no direct way to filter items by the URL or the description via the Get items Filter Query. For example, you might hope to do DocumentationLink/Url eq 'http://contoso.com/manual.pdf', but SharePoint will return an error or simply not filter correctly. The SharePoint REST API cannot filter on the Url or Description subfields using OData.

So, there isn’t a valid Field eq value syntax to demonstrate here – any attempt will result in “unsupported query”. If you absolutely need to query by a hyperlink, see the notes below for workarounds.

Notes & Pitfalls:

No OData support: Microsoft’s documentation and community consensus confirm that you cannot filter by hyperlink columns with the REST API filter. It’s a limitation [sharepoint.stackexchange.com]. This is because the field is complex and not treated as a simple queryable property.
Possible workaround via CAML: One workaround is using a CAML query (Collaborative Application Markup Language) inside an HTTP request. For example, you could use a SharePoint HTTP action with the CAML <Where><Eq><FieldRef Name="DocumentationLink"/><Value Type="URL">http://contoso.com/manual.pdf</Value></Eq></Where></Query> to filter by URL [sharepoint.stackexchange.com]. When combined with the REST API (_api/web/lists/GetByTitle('X')/GetItems(query)), this can retrieve filtered results. However, this is an advanced technique requiring manual crafting of CAML, and it must be done via the HTTP action (not the standard Get items).
Filter after retrieval: If you cannot use CAML, another approach is to retrieve items without filtering (or with some other filter to reduce scope) and then use a Filter array in Power Automate to check the hyperlink field. The flow can examine the FieldName object which typically has {'Url': 'http://...', 'Description': '...'} in the JSON. You could filter where item.DocumentationLink.Url contains or equals a certain string.
Partial matches: If you wanted to filter by those whose URL contains a domain or path (e.g., all links to contoso.com), you definitely can’t do that server-side with OData. Your best bet is pulling items and filtering in memory.
Description filtering: Similar story – you can’t do OData filter for the Description of a hyperlink field. If that’s needed, you’d again have to do an in-flow filter or CAML query.
Null/blank: One thing you can do with OData is check if the hyperlink field is empty. For example, DocumentationLink eq null will likely return items with no link (since the field value would be null). This works because checking for null doesn’t require looking into the Url property specifically – if there’s no hyperlink, the whole field is null. Conversely DocumentationLink ne null gives items where a link is present. This can be useful (and is supported).
Pitfall – trying to filter URL by text: A very common attempt is something like DocumentationLink eq 'http://contoso.com/manual.pdf'. This will not work (the API doesn’t magically apply that to the URL subfield).
Conclusion: When it comes to hyperlink fields, your strategy should be either to filter on a different field if possible, or handle it in the flow. If you must do it at the source, be prepared to use the HTTP action with CAML queries (which is beyond beginner level and has its own complexities).

Filtering a Managed Metadata (Tag) Column (Single Value)

SharePoint Column Setup: Managed Metadata columns (also known as taxonomy or tag columns) allow you to tag items with terms from a Term Store. Each value is a term with a unique ID (GUID) and a textual Label. In a single-value managed metadata column, each item can have at most one term selected. The data stored is a special taxonomy field type, and SharePoint also maintains hidden supporting columns (like a text field with “Term|GUID” and a hidden list that maps terms, etc.). Importantly, the SharePoint REST API doesn’t treat the term label or ID like a normal field for filtering.

Filter Query (Managed Metadata Example): Direct filtering on a taxonomy field is not possible with OData in Get items. If your column is “CategoryTag” and you try CategoryTag eq 'Financial' or any variant, it will not work (the field is complex, and SharePoint will reject the query).

However, there is a known workaround using a hidden column called TaxCatchAll. SharePoint’s Taxonomy feature populates a hidden multi-value field TaxCatchAll on each item with references to all terms used (across any taxonomy fields on that item). This field has sub-properties like Term (the term’s text) and ID (a lookup ID to a hidden Taxonomy list). We can leverage this to filter items that have a given term. For example, to find items where the CategoryTag is “Finance” (or any taxonomy field on the item contains "Finance"):

TaxCatchAll/Term eq 'Finance'

or 

TaxCatchAll/IdForTerm eq '[GUID of Finance]'

This will return items that have the term Finance applied [teamdynamix.umich.eduteamdynamix.umich.edu]. We’re essentially asking SharePoint: in the TaxCatchAll bag of terms for the item, is there one whose Term (text) equals "Finance".

Notes & Pitfalls:

Broad vs Specific: TaxCatchAll/Term eq 'Finance' doesn’t specifically target the CategoryTag column – it targets any term on the item with that label. If the item has another taxonomy column that also could have a “Finance” term, it would still be returned. In many cases, you might only have one taxonomy column, so it’s fine. If you have multiple, this query might over-include, in this case, using TaxCatchAll/IdForTerm will be the better option.
Single vs multi taxonomy: For single-value taxonomy fields, each item has at most one term in that field, but TaxCatchAll is still multi-valued (it holds an array of terms from all taxonomy fields). For multi-value taxonomy (covered next section), an item can have multiple terms from one field, but from TaxCatchAll perspective it’s all just additional entries in the same bag. So the approach using TaxCatchAll works for both single and multi – it’s basically “does the item have this term anywhere in its taxonomy fields”.
Term text vs GUID: We used the term’s text (label) in the query. That is often sufficient and more human-readable. But beware of duplicates: if two different terms in the term store have the same name (even in different term sets), TaxCatchAll/Term eq 'Finance' will fetch items tagged with either one. If you need to distinguish terms with the same label, you might need to filter by the Term’s ID (GUID). However, the TaxCatchAll data doesn’t expose the GUID directly, only an ID which is a lookup ID in a hidden list. Not trivial to use in OData. In such cases, using an HTTP request with CAML might be easier, where you can specify the value’s GUID in the query.
CAML/HTTP alternative: SharePoint’s CAML query can filter taxonomy fields by their GUID or by the term label within a specific field. If you absolutely need precision (e.g., “CategoryTag contains Finance term specifically”), you could consider an HTTP action with a CAML like:
<Where><Eq><FieldRef Name='CategoryTag'/><Value Type='Text'>Finance</Value></Eq></Where>
But actually, because taxonomy fields store a “Label|GUID” in a hidden note field, a CAML might need to use <Contains> on that hidden note. It gets complicated and often not worth it unless absolutely necessary.
Indexing and thresholds: Managed Metadata columns are treated like lookup columns (each term is essentially a lookup to the term store’s data). SharePoint does not allow indexing on a taxonomy field. This means any filter on taxonomy fields is non-indexed. If your list is beyond 5000 items, a direct filter like TaxCatchAll/Term eq 'Finance' may fail once the list hits the threshold (because it’s effectively filtering on a multi-value lookup without an index). If you expect a large list, consider filtering by some other criteria first (like a date or a location that narrows it down) and maybe combine with the term filter if possible. Or use the HTTP + CAML approach, which might handle larger sets (CAML can query by indexed columns first then filter by others).
Performance: Even on smaller lists, the TaxCatchAll approach might be slower than a normal filter, because it has to join with the hidden taxonomy data. But it’s usually fine for moderate sizes.
Security: Managed metadata filters via TaxCatchAll should only return items the user (or flow’s connection) has access to, just like normal filters.
Multiple terms in one item (single field): If an item has the term "Finance" and "Budget" in a multi-value field, the query Term eq 'Finance' will still return that item (because one of its terms is Finance). That’s expected. If you needed items that have both Finance and another specific term together, that’s beyond what OData can do directly – you’d need to filter one term and then post-filter for the other in flow, or something of that sort.
Pitfall – using the field name directly: Some might try CategoryTag/Label eq 'Finance' or CategoryTag eq 'Finance'. This will not work; the field doesn’t expose a property for label or ID via the OData filter. Always go through TaxCatchAll or an HTTP query.
Example: Let’s say you have a “Topic” column (taxonomy) and you want all items tagged with “Safety”. Use TaxCatchAll/Term eq 'Safety'. That will bring items where any taxonomy field has "Safety". If you have only the Topic field that uses such terms, you’re good. If you also have another taxonomy field “DepartmentTags” that could also have a "Safety" term (maybe in a different term set), those items will also come. If that’s a concern, and if at least the term sets are distinct enough that the term labels don’t overlap, you might be okay. If not, you may need to refine the approach (like filter by TermSet via the lookup ID, which might be possible by filtering TaxCatchAll/ID – but identifying the correct ID is complex since it refers to an entry in the hidden TaxonomyHiddenList).
No partial matches on term: TaxCatchAll/Term uses eq, so it’s exact match of the term text. There is no startswith or contains for terms in OData filter. If you needed something like “terms that contain 'Finance' in the name”, you’d have to retrieve and filter in flow.
Summary: For a beginner, the key takeaway is: You generally cannot filter directly on a Managed Metadata column. The practical solution for basic needs is to use TaxCatchAll/Term eq 'YourTermName' to get items tagged with that term [teamdynamix.umich.edu]. Just remember its limitations. If your scenario is too complex for this, it might be beyond “beginner” territory and require more advanced queries or design changes.

Filtering a Managed Metadata Column (Multi-Select)

SharePoint Column Setup: A multi-select Managed Metadata column allows multiple terms (tags) to be assigned to an item from the term set. This behaves conceptually like a multi-choice, but with terms. In an item’s data, it could have an array of terms, each with a label and GUID. SharePoint will list all those terms in the TaxCatchAll for the item.

Filter Query (Managed Metadata Multi Example): The approach is the same as for single-value: use TaxCatchAll. If we want items that have (at least) the term “IT Services” in a multi-tags field, we do:

TaxCatchAll/Term eq 'IT Services'

This will return any item that has "IT Services" as one of its tags (or any term with that exact text) [teamdynamix.umich.edu]. It does not matter if the column allows one or many – the query doesn’t change. It finds the term in the item’s term collection.

Notes & Pitfalls:

Inherits all the single-value notes: All the caveats mentioned for single-value taxonomy apply here as well: no direct field filtering, uses TaxCatchAll, watch out for duplicate term names, no indexing, etc. In fact, TaxCatchAll was designed to handle multi-value taxonomy, so it’s exactly what we leverage.
Multiple terms matching vs all terms: The query Term eq 'X' checks for at least one occurrence. If you need items that have all of a set of terms (e.g., items tagged with both "IT Services" and "HR"), OData can’t do an “AND” on two occurrences of TaxCatchAll easily because it would interpret it as needing one term that equals X and another term that equals Y in the same item. If you try something like (TaxCatchAll/Term eq 'IT Services') and (TaxCatchAll/Term eq 'HR'), the query will likely return nothing, because no single term in the catch-all can be both "IT Services" and "HR" simultaneously. The filter doesn’t understand it should look at two different terms. To achieve an “AND” of tags, you’d again have to retrieve items with one tag and then filter for the second tag in flow, or use a different strategy (maybe an alternate field that tracks combined tags, etc.). Generally, filtering by multiple required tags is advanced.
OR of terms: On the other hand, if you want items that have either "IT Services" or "HR", you can do (TaxCatchAll/Term eq 'IT Services') or (TaxCatchAll/Term eq 'HR'). This should return items that have either term (or both). It basically checks if there exists a term equal to IT Services or exists a term equal to HR. This is a valid approach for an “inclusive OR” of terms.
Performance of multiple or: Combining multiple OR conditions on TaxCatchAll might get slow if there are many terms. But if just a couple, it should be fine.
Term subsets: If you wanted to filter by any term under a certain parent in the term hierarchy – e.g., any term under "Technology" – OData filter won’t do that (you’d have to retrieve all and filter by term path maybe in code, or query the term store separately). That’s beyond OData scope.
Check for empty taxonomy field: If you want items where the taxonomy field has no value (i.e., no terms selected), you can filter on the column’s value being null. E.g. CategoryTag eq null might work (I say "might" because taxonomy fields often come through as an object – but if it’s empty it’s likely null). Alternatively, TaxCatchAll/Term eq ' ' (an impossible term) wouldn’t really help. Stick to CategoryTag eq null in the OData filter; that usually returns items with no tag in that field.
Examples: If a list item’s multi-tags have ["IT Services", "Networking"], the query Term eq 'IT Services' will catch it. The query Term eq 'Networking' will also catch it. Both together with OR will catch it (duplicate, but results are unique items anyway). An AND of both would miss it because no single term entry equals both (as explained).
Pitfall – partial term names: Just to reiterate, you cannot do partial matches like substringof('Service', TaxCatchAll/Term) in the Get items filter – it doesn’t support using functions on the TaxCatchAll multi-value like that (and likely would error out). So you need the full term name.
When taxonomy filter fails: If you attempt the TaxCatchAll approach on a list over 5000 items and get an error or empty result, you likely hit the threshold. There’s not an easy fix except to reduce scope or use other filters in combination. One trick: if your term column itself is configured as indexed in the term store (this is separate from list indexing – term store has something called tagging or pinned terms?), it doesn’t matter, SharePoint still doesn’t index at list item level for query. So no relief there. You could consider maintaining a shadow choice column that replicates the tags (if your term set is somewhat static and not too large). But again, that’s a workaround outside of pure OData.
Wrap-up: Use the TaxCatchAll technique for simple “has this term” queries, but manage expectations for more complex taxonomy queries.

Example: Combining Multiple Filter Conditions

So far we’ve looked at filtering by one column at a time. In real-world scenarios, you often need to filter by multiple criteria simultaneously – for instance, “Title contains X and Status is Y and Created after Z”. OData filter queries support combining conditions with and / or as discussed in the basics. Let’s build a full example:

Scenario: We have a SharePoint list of projects with columns: Title (Text), Status (Choice), StartDate (Date), and Approved (Yes/No). We want a view of projects where Title contains “Alpha”, Status is “Active”, StartDate is after Jan 1, 2023, and Approved is Yes.

Filter Query: We can write this as:

substringof('Alpha', Title) and Status eq 'Active' and StartDate ge datetime'2023-01-01T00:00:00Z' and Approved eq 1

Breaking it down:

substringof('Alpha', Title) – Title contains "Alpha" (anywhere in the title)
Status eq 'Active' – Status equals "Active"
StartDate ge datetime'2023-01-01T00:00:00Z' – StartDate on or after Jan 1, 2023 (we used ge with midnight of that date)
Approved eq 1 – Approved is Yes (remember 1 = true for Yes/No)

All conditions are combined with and, meaning an item must meet all four to be returned.

We could also add parentheses for clarity, but since it’s all ANDs, it’s not strictly necessary. It might look like: (substringof('Alpha', Title)) and (Status eq 'Active') and (StartDate ge datetime'2023-01-01T00:00:00Z') and (Approved eq 1) – which is equivalent.

Using this in Power Automate: You would paste this string into the Filter Query field of Get items. Ensure there are no extra quotes around the whole thing (the field itself should just contain the query, not "query" in quotes). Also, be careful with the single quotes inside the query when writing in Power Automate: You often need to use single quotes around values. In this example, the query already uses single quotes correctly. If you build it dynamically, don’t accidentally double quote them.

Things to note:

We utilized a text function substringof alongside simple comparisons. This is allowed.
If we needed an OR condition (e.g., Status is Active or Active (Delayed) perhaps), we’d use or, but then grouping becomes important. For example: (Status eq 'Active') or (Status eq 'Active (Delayed)') and Approved eq 1. Due to operator precedence, that query would be interpreted as everything with Status 'Active' or (Status 'Active (Delayed)' and Approved is Yes). If you intended the Approved Yes to apply to both, you should group: ((Status eq 'Active') or (Status eq 'Active (Delayed)')) and Approved eq 1. Always use parentheses to clarify intended logic.
Delegation considerations: If any part of this combined filter is non-delegable (for instance, substringof on Title in SharePoint is not indexed, but SharePoint does allow that filter up to 5000 items; it’s not strictly “delegation” since this is server-side already. Let’s just say if Title isn’t indexed and the list is huge, this might falter). If you had an indexed field in there (Status could be indexed if choice, StartDate could be indexed if date), SharePoint might still not execute if the first part of the filter it sees is substringof. Unfortunately, SharePoint’s query engine doesn’t always smartly use the indexed part when using functions on another part. A trick is to reorder conditions to put an indexed condition first (it sometimes helps). E.g., StartDate ge datetime'2023-01-01T00:00:00Z' and substringof('Alpha', Title) and Status eq 'Active' and Approved eq 1. It’s not guaranteed, but it might short-circuit some scanning. In any case, test on large lists.
Testing the combined filter: It can be helpful to first test simpler subsets (e.g. confirm substringof('Alpha', Title) returns expected, then add Status eq 'Active', etc.) to isolate if something is off. Common errors in combined filters are usually typos or missing quotes/parentheses.
Flow debugging: If the Filter Query is invalid, the Get items action will fail with a bad request error usually telling you “Invalid filter clause”. The error might point to the problematic segment. Double-check internal names and syntax in that case.

Applicability to “Get files” and others: The above example would work similarly in a Get files (properties only) action if those were columns on a document library (Title in a library might be the name or a separate Title field, Status and Approved could be custom columns, etc.). Just remember to adjust field names as needed. For file system, the filter might be on FileLeafRef (the internal name for file name) for example. But the syntax and idea remain OData compliant.

In summary, combining conditions lets you create powerful queries to slice your data in one go. Always use proper parentheses and be mindful of internal names. With a bit of practice, you can represent quite complex logic directly in the Filter Query, making your flow far more efficient than pulling everything and filtering later [sharepains.comteamdynamix.umich.edu].

Notes and Common Pitfalls

Before we conclude, here’s a summary of common issues and tips when using OData filter queries in Power Automate:

Internal Name vs Display Name: This bears repeating – always use the internal column name in the query [teamdynamix.umich.edu]. If you get no results or an error, double-check the internal name (especially if the column was renamed or has spaces/special chars). The internal name can be found in the list’s URL when editing the column settings.
Quotes and Data Types: Use single quotes around strings and date literals. Do not use quotes around numbers or booleans. If a value itself contains a quote, escape it by doubling ('O''Reilly' for O'Reilly). If you forget quotes around a text/date value, you’ll get an error “Incorrect type” or “Unexpected token”.
Case Sensitivity: OData operators and functions must be lowercase (eq, and, substringof, etc.). Also, function names like startswith and substringof are all lower case [teamdynamix.umich.edu]. Using StartsWith or SubstringOf will fail.
Boolean Values: Remember for SharePoint: true/false are not used in filter; use 1/0 for Yes/No columns [teamdynamix.umich.edu]. (In some other connectors like Dataverse, you might use true/false, but not in SharePoint’s case).
Null Checks: To filter blanks, use Field eq null or Field ne null. Don’t put null in quotes.
Multiple Choice/Lookup/People: These multi-valued fields don’t support direct equals comparison for a single value [learn.microsoft.com]. Use workarounds like substringof for multi-choice text or the /Email or /Title approach for multi-people, understanding the limitations as discussed.
Delegation and List Threshold: If your filter query isn’t returning items you know exist, consider the list size and indexing. It could be that SharePoint is refusing to execute the query due to threshold. Check if the field is indexed. If not, try indexing it (if possible) or filtering on an indexed field in addition. Or break the query into two (e.g., filter by an indexed column in OData, then further filter in the flow for the finer condition).
Test incrementally: If you have a complex filter that isn’t working, build it up piece by piece. Test each part in isolation (maybe in a separate Get items or using the REST API in a browser) to ensure each condition works, then combine them.
Special Columns: A few columns have unique internal names or behaviors:
- Title field (default title) internal name is Title (easy).
- The ID field (item ID) internal name is ID (all caps). You can filter by ID, e.g., ID eq 100.
- For document libraries: the Name (file name) is stored in the FileLeafRef field (internal). So to filter files by name: FileLeafRef eq 'Report.pdf'.
- The file’s Title (which is a separate field, often blank unless set) is just Title.
- “Modified” date internal is Modified, “Created” is Created (simple enough). But “Modified By” (a person field) is Editor internally, and “Created By” is Author internally.
- If you use those in filter: e.g., Author/EMail eq 'bob@co.com' for created by Bob.
No wildcards: There’s no wildcard * or ? in OData filters. Use substringof or startswith for text contains or prefix matching.
Max characters: The Filter Query field in Power Automate can hold quite a long string, but extremely long queries might hit a URL length limit on the SharePoint API (because behind scenes it’s constructing a REST URL). This usually isn’t an issue unless you try to filter on something like 50 values with ORs. If you find you’re approaching a URL length issue, consider alternative designs (maybe break into multiple queries or use a different method).
Encoding: Power Automate should handle encoding of special URL characters for you (like spaces become %20 automatically in the call). If you manually put %20 in your filter string it’s not necessary – just use the raw characters as described (spaces, etc., within quotes).
Error messages: An “Invalid filter clause” error usually means syntax error or invalid field name or value type. An “Unsupported or invalid filter” might mean you used an unsupported operation (like filtering on a hyperlink or multi-lookup). A “The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold” means your query tried to use too many lookup fields on a large list – reduce complexity or number of lookup columns referenced.
Other actions: Remember, you can use the same filter syntax in Get files (properties only) for libraries, in Get items for Office365 Groups (though their fields differ), and in other connectors like Outlook messages (fields differ and not all functions are available). Always refer to that connector’s documentation for specifics. For SharePoint, Microsoft’s docs on OData filters are the go-to reference [learn.microsoft.com].

By keeping these tips in mind, you’ll avoid most of the common frustrations with OData filter queries in Power Automate.

References

Microsoft Learn – Using OData Filters in SharePoint REST: Official documentation on OData query operations in SharePoint REST API, including supported $filter operators and examples [learn.microsoft.comlearn.microsoft.com]. This covers how SharePoint handles filters for lookup fields, users, etc., and the limitations (e.g. no filtering multi-value lookups directly).
TeamDynamix Knowledge Base – “How to use OData query filters in the SharePoint Get Items action for Power Automate”: Gabriel Mongefranco’s detailed tutorial with examples for people fields, lookup fields, taxonomy, yes/no, etc. It explains using TaxCatchAll for tags and cautions about multi-lookup filtering [teamdynamix.umich.eduteamdynamix.umich.edu]. A great resource for advanced SharePoint filtering scenarios.
Tom Riha – OData Filter Query Cheat Sheet: A handy PDF cheat sheet that lists filter query syntax for various SharePoint column types (e.g., Yes/No as Field eq 1, Person as Person/Email eq 'value', Lookup as FieldId eq 123) [tomriha.comtomriha.com]. Good for quick reference when you forget the exact syntax.
SharePains by Pieter Veenstra – “SharePoint OData filter query in Get items actions in Power Automate”: A blog post illustrating the benefits of server-side filtering to optimize flows [sharepains.com]. Includes tips like using parentheses in combined filters and examples of common filter strings. Helps reinforce best practices to avoid the “wrong way” of filtering after retrieval.
SharePoint StackExchange – Filtering Hyperlink Columns: Community Q&A confirming that direct OData filtering on Hyperlink (URL) fields is not possible [sharepoint.stackexchange.com]. Provides a CAML query workaround for those who need to filter by URL via the REST API. Useful to understand platform limitations.
Power Automate Community Forum – Multi-Select Filtering Discussions: Various threads (on Reddit and Power Users forum) discuss the challenge of filtering multi-select choice or people fields. For instance, using substringof for multi-choice [reddit.com] and why an eq query might not work. These discussions reinforce why workarounds are needed and what their trade-offs are.

By mastering OData filter queries, you can make your Power Automate flows far more efficient and maintainable. Instead of pulling massive datasets and looping, you let SharePoint do the heavy lifting. The syntax can be tricky at first, but with resources like this guide (and the references above), you’ll soon be crafting filter strings like a pro. Happy automating! [sharepains.comteamdynamix.umich.edu]