OData: Not Equals Operator

We've already covered the "equals" operator but let's look at its counterpart, the "not equals" operator. It provides a useful filter of data that we don't want to fetch from the server. Where the "equals" operator gets us what we want, here we exclude what we don't want, parsing out the data exceptions. It's also quite useful to find incorrect results. You can define an "equals" expression where you define what the data "should be" and then use the "not equals" operator to find data that is incorrect, for example.

There are many areas in the Power Platform and more where the OData "not equals" operator is useful. Still, I'll use Power Automate as an example to demonstrate its usage. OData is a standard, so everything here carries over to any RESTful API that supports it.

Usage

We need to know that the "not equals" operator is represented as "ne" in the OData syntax. Let's check an example using SharePoint Lists and Power Automate.

To test it, I've created a list of fake people with some random data.

Let's use Power Automate as our testing tool to fetch information using the "not equals" operator. The expression goes in the "Filter Query" box, inside the advanced options of the "Get Items" action.

Strings

Let's say that we want to fetch everyone that isn't called "David." We know that the column is called "First Name," so let's build our expression:

First_x0020_Name ne 'David'

Here's the test Power Automate Flow. We use a table to see the results quickly and check if our filter is working.

Here's the result:

As we can see, we don't get any rows with "David." The result will return the value "First_x0020_Name," so this way, you're sure that you're filtering using the correct value.

Values with apostrophes

If the value contains a single quote, double it. A single quote ends the string, so a lone one cuts the value short.

Last_x0020_Name ne 'O''Brien'

This is the standard OData way, and Dataverse documents it too. Dataverse also ignores upper and lower case in string filters, so 'david' and 'David' match the same rows.

Integers or Floats

The same works for Integers or Floats. Let's try to figure out who's not 29. To do that, here's the expression.

Age ne 29

Using the same strategy as before:

Here's the result:

Notice that we're not using quotes here since we don't need them.

How about a number column that is a "Currency" in SharePoint. SharePoint stores the value as a number, so you can use the same expression as before, and you're fine.

Boolean fields

In the case of boolean comparisons, you need to use the 1 or 0 notation. 1 for true and 0 for false. Here's the expression for the Employee column:

Employee ne 0

Don't try to use "true" or "false" or any other notation, since that is reported to return every row instead of filtering. Here's the test case:

And the result:

Lookup columns

A single-value lookup column has an ID and a title. Filtering on the ID is the most reliable choice, because it doesn't change if someone renames the item.

Country/Id ne 4

Here 4 is the ID of the item in the lookup list.

Combine types

You can mix the "not equals" operator with the "and" and "or" operators like any other comparison. Here's everyone that isn't David and isn't 29:

First_x0020_Name ne 'David' and Age ne 29

Reading it left to right, the first half drops every David, the second half drops everyone aged 29, and the "and" means a row has to survive both conditions to come back to us.

The same operator in Dataverse

Dataverse uses the same "ne" operator in the "Filter rows" box of the "List rows" action, so the skill carries over. Two column types need a slightly different shape. Choice columns compare by their numeric value, and lookup columns are queried through the underlying value column with the row's GUID, which is its unique ID:

statecode ne 0

_primarycontactid_value ne 3f7bb2e0-1f8c-4a1a-9f2f-2c5d0f0a1b23

Microsoft lists the full set of comparison operators for Dataverse in the Filter rows using OData reference.

Non-intuitive behaviors

Blank values may drop out of the results

This is the one that surprises people, and it's worth knowing before you trust a "not equals" filter. The OData standard says a blank value is not equal to anything but itself, so a "not equals" filter should return the blank rows. Dataverse documents it differently. The Filter rows page says: "The response includes only records where the expression evaluates to true. Records aren't included if the expression evaluates to false or null."

Services don't always agree here, so test with a blank row before you trust the filter. If the blanks matter to you, catch them on purpose:

Status ne 'Approved' or Status eq null

The "or" part brings the blank rows back, so the filter reads as "everyone who isn't approved, including those with no value."

Multi-value columns can't be filtered at all

Person or Group columns that accept several people and multi-value Lookup columns are off the table. The SharePoint REST service is clear on why: "Because multi-value lookup fields are returned as a string of multiple values, there's no way to query for them." No "ne", no "eq", nothing.

Single-value versions of the same columns are fine. For a single-value lookup you query the referenced list's column, like this:

Country/Title ne 'New Zealand'

For the multi-value versions, fetch the items and filter them with the "Filter array" action instead.

It's a comparison, not a text search

The "ne" operator is supported for both numeric and string comparisons, but the string toolbox on the server is small. "endswith", "replace", "substring", "tolower", "toupper", "trim" and "concat" are all unsupported, and so are the arithmetic operators and the math functions. So if you were hoping to write something like "the title doesn't end with 'draft'", that comparison isn't available server-side. You'd have to pull the rows and use the "Filter array" action instead, which is a lot more expensive, but it does the job.

Limitations

Field names, not display names

The main thing to keep in mind is the name of the fields that you're querying. Since you can use this strategy for multiple sources, you should be careful and check the names before you build the filter.

URL size

Filters travel as part of the request URL, and very long URLs are eventually rejected. Microsoft doesn't publish a limit for the "Filter Query" box, so keep the filter short. For a long list of exclusions, fetch the rows and remove them with the "Filter array" action instead.

The 5,000 item ceiling

The "Get Items" action returns 100 items by default. You can raise the "Top Count" up to the SharePoint list view threshold of 5,000 (the limit SharePoint puts on how many items one query may scan), but ask for more than that and the action fails. Your filter runs on the server, so a good "not equals" expression is exactly what keeps you under the ceiling. A lazy one that only removes a handful of rows won't help you at all.

Dataverse caps the number of conditions

If you chain a long list of exclusions in Dataverse, there's a hard stop at 500 conditions in a single query. Go over it and you get a TooManyConditionsInQuery error. It's a generous limit, but it's reachable if you're building the filter dynamically from a list of values. Split the exclusions into smaller queries, or fetch the rows and remove the unwanted ones with the "Filter array" action.

Troubleshooting Common Errors

The Flow returns nothing even though matching items exist

Cause: On lists with more than 5,000 items, Microsoft documents that "you may observe that no records are returned if there are no items matching the filter query in the first 5000 items." The filter is applied, it just never reaches the rows you wanted.

Solution: Turn on Pagination in the action's settings menu. Once it's on, the action keeps asking for the next batch instead of stopping at the first one.

The number of items in this list exceeds the list view threshold

Cause: SharePoint throttles queries that touch too much of a large list at once. The full message is "The number of items in this list exceeds the list view threshold, which is 5000 items. Tasks that cause excessive server load (such as those involving all list items) are currently prohibited."

Solution: Index the column you're filtering on and make the filter narrower, so the query returns well under 5,000 rows. This is where the "not equals" operator can work against you, since excluding one value usually still leaves almost everything behind. Pair it with an "and" condition on an indexed column that does the heavy lifting.

The query exceeds the lookup column threshold

Cause: SharePoint limits how many lookup, Person or Group, and Managed Metadata columns one query can touch. In SharePoint Online the limit is reported as 12. The error reads "The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold."

Solution: Ask only for the columns you need, and fetch the rest with a second action if required.

Recommendations

Here are some things to keep in mind.

If possible, prefer the "equals" operator

People tend to think in a "positive" way. Something equals something. Querying for something that "isn't" something is a shift of mindset, so use it only when it makes sense. Prefer the "equals" operator to get the data that you want. It's a lot more work to filter the data that we don't want than to filter only the data we want.

Often the "not equals" operator is used to remove exceptions. Be careful with this since more exceptions may come that are not considered in your expression and result in invalid data.

Use it when it makes "sense"

Depending on how your columns are named, you may want to use the "not equals" operator instead of the "equals" one. For example, if a column is named "Is Consultant" and you want to get the employees, you can use the "equals" to false, but it makes more sense to read it as "is a consultant not equals to true." The result is the same, but how the expression is written makes its intention much clearer.

It's ideal for cleaning data

I've mentioned above, but the "not equals" operator is ideal to find edge cases or information that is not correct. You can build an expression that describes what the data should look like, and anything it returns is a row we need to clean. For example, a column with a pre-defined list of values that has a value which is not on that list should be flagged. This is only possible with a "not equals" operator since you're looking for something that should not be there.

It's ideal for hiding data

Another useful way to use it is to hide data that you don't want to see. You can have a column for soft-deletion called "Is Deleted." Having the data that is "not deleted" is quite useful and makes data recovery easier. Use it with care since if you forget to filter the data in your code, you can display data that should not be there.

Combine with care

You can combine multiple operators but keep them to a minimum. Keeping expressions as small as possible is a good practice. But the main reason is debugging. If something comes from the source that you don't expect, you'll have a hard time knowing what conditions are the correct ones, so keep things simple to debug things easily.

Name it correctly

The default view of actions like the "Get Items" action hides the advanced options, so someone scanning your Flow may not notice there's a filter at all. Rename the action to say what it returns, so the filter isn't a hidden surprise.

Always add a comment

If you can, regardless of where you use the expressions, try to add a comment indicating what you are trying to do. Do this before you do the expression so that you think before you do. It's a good practice to map intention with action so that, if something goes wrong, you can check either the initial information or the expression itself.

Final Thoughts

The "not equals" operator is a small piece of syntax that does a lot of work, especially when you're hunting for the rows that shouldn't be there. Keep an eye on the blanks and on the size of the list you're querying, and it will serve you well. And when in doubt, ask yourself if the "equals" operator would say the same thing more simply.

Back to the OData Reference.

Photo by Arie Wubben on Unsplash