excelavon

Power Query Not Equal: Use the <> Operator

How-to basics · Updated November 9, 2026

Power Query Not Equal: Use the <> Operator” loading=”lazy” width=”800″ height=”450″ style=”max-width:100%;height:auto” /></p> <p>In Power Query’s M language, the not-equal operator is <code><></code>. For example, <code>[Status] <> "Closed"</code> is true when a row’s Status value is not Closed; in the filter menu, the equivalent option is usually called Does Not Equal.</p> <h2>What “not equal” means in Power Query</h2> <p>Power Query is the data-import and transformation tool in Excel and Power BI. Its formulas use the M language, which is different from Excel worksheet formulas. In M, write the less-than sign followed by the greater-than sign, with no space between them: <code><></code>. This is the comparison operator for asking whether two values differ.</p> <p>For instance, comparing a category with a text value looks like <code>[Category] <> "Supplies"</code>. The expression returns a logical value: true if the values differ and false if they match. The square brackets refer to a field in the current row, and text values need quotation marks. In a filter step, Power Query uses that true-or-false result to decide whether to keep each row.</p> <p>It is easy to confuse this operator with Excel’s <code><></code>, because Excel uses the same symbols for not equal. The surrounding formula syntax is not the same, however: Power Query expressions are M, not cell formulas. For example, M uses <code>null</code> for a missing value, whereas an Excel worksheet formula commonly checks for an empty cell with a reference such as A2.</p> <h2>Using the operator in a custom column</h2> <p>A custom column is useful when you want to label rows rather than remove them. In Excel for Microsoft 365 on Windows, open the query in Power Query Editor, select Add Column > Custom Column, enter a name, and type an expression in the formula box. For example, if the field is Status, enter:</p> <p><code>[Status] <> "Closed"</code></p> <p>The result is a True/False column. Rows with Status equal to Closed receive false; other nonmatching values receive true. You could then filter the new column to true, use it in a later transformation, or keep it as a flag for an audit.</p> <p>To return words instead of logical values, use an M conditional expression:</p> <p><code>if [Status] <> "Closed" then "Keep" else "Exclude"</code></p> <p>The Custom Column dialog expects the expression for the current row, so you normally do not add <code>each</code> there. When writing a table operation directly in the Advanced Editor or formula bar, <code>each</code> commonly introduces the per-row expression. A complete example is:</p> <p><code>Table.SelectRows(Source, each [Status] <> "Closed")</code></p> <p>This example assumes the preceding query step is named Source and the table has a Status column. The result is a table with rows for which the comparison is true.</p> <h2>Filtering rows with Does Not Equal</h2> <p>You do not have to write M code to exclude a particular value. In Power Query Editor, open the filter dropdown for the relevant column, choose Text Filters, Number Filters, or the matching filter type, then select Does Not Equal and enter the value. The available wording and choices depend on the column’s data type.</p> <p>For a Status column, choosing Does Not Equal and entering Closed is a convenient way to remove closed records. Power Query adds a step to the Applied Steps list and generates M behind the scenes. Select the step to review it, or use Home > Advanced Editor to inspect the query. Seeing the generated expression is a practical way to learn how the graphical filter corresponds to M.</p> <p>Before loading the result, check the preview and confirm that the records you meant to keep are still present. In particular, decide whether blank values should remain. A filter that excludes one named value is not necessarily the same as a rule that keeps only a known set of values; those are different requirements.</p> <h2>How null and blank values behave</h2> <p>Power Query represents a missing value as <code>null</code>. In M equality comparisons, null is treated as a value: <code>null = null</code> is true, and <code>null <> "Closed"</code> is true. Therefore, a row with a null Status can satisfy the condition that Status is not Closed. This is often useful when the goal is simply to remove closed rows, but it may surprise someone who expects missing values to be discarded automatically.</p> <p>If the rule is “keep rows that have a status and that status is not Closed,” make the null check explicit. In a custom column, one readable expression is:</p> <p><code>[Status] <> null and [Status] <> "Closed"</code></p> <p>The first condition rejects nulls; the second rejects Closed. Conversely, if the requirement is to keep only records where Status is null or equals a particular value, express those conditions directly with <code>or</code>. Explicit logic makes the intent easier to review than relying on assumptions about blanks.</p> <p>A blank-looking cell from a source file is not always the same thing as a text value containing spaces. A true missing value is null, while a cell containing a space is text. If imported data has inconsistent blanks, you may need to trim or clean the text first, or replace empty strings with null, before applying the comparison. Inspect representative rows in the preview to learn what the source actually contains.</p> <h2>Comparing text, numbers, and dates</h2> <p>The values on both sides of the operator should have compatible types. Comparing a text field with a text literal is straightforward; comparing a numeric field with a number should use a number, not a quoted text value. For example, a numeric Quantity column can be checked with <code>[Quantity] <> 0</code>. Writing <code>[Quantity] <> "0"</code> compares it with text and can produce a type-related problem or a result different from the one intended.</p> <p>Dates should also be compared as dates rather than as text that happens to look like a date. If a source column was imported as text, first set or convert its data type, then compare it with a date value using valid M date syntax. Type conversion can fail when source values are malformed or use an unexpected regional format, so check the query for errors after changing types.</p> <p>Text comparisons in M are case-sensitive by default. That means a value such as closed may not compare as equal to Closed. If the source is inconsistent about capitalization, normalize the text before comparing or use a deliberate case-insensitive comparison. Do not silently assume that a not-equal filter ignores capitalization; verify the behavior against the actual values in the column.</p> <h2>Column names, types, and common errors</h2> <p>A common error is referring to a column incorrectly. Simple names can be written as <code>[Status]</code>. If a name contains spaces or punctuation, M field access often uses the quoted identifier form, such as <code>[#"Order Status"]</code>. Use the exact column name shown in the query, including spaces. Renaming a column in an earlier step can also make a later expression that uses the old name fail.</p> <p>Another frequent issue is a type mismatch. Check the small data-type icon beside the column name and confirm that it matches the values you intend to compare. A column marked ABC is text; a numeric or date icon indicates a different type. If the source mixes numbers and text, fix or handle the inconsistent values before building a comparison that assumes one type.</p> <p>When a comparison returns errors rather than true or false, examine the affected rows and the Applied Steps. A prior conversion may have produced errors, a field name may be misspelled, or the expression may be comparing incompatible values. The Power Query preview can display an error for a step even when the source file looks ordinary, because the query applies transformations in sequence. Select an error cell to inspect its details, and correct the earliest relevant step rather than masking the symptom later.</p> <h2>Choosing between not equal and other filters</h2> <p>Use <code><></code> when the rule is specifically to exclude one value. If you want to keep only one value, use equality, written <code>=</code>. If you want to keep several allowed values, a list-membership test or a sequence of conditions may better express the rule. For example, two exclusions can be combined with <code>and</code>: <code>[Status] <> "Closed" and [Status] <> "Cancelled"</code>. Both comparisons must be true for a row to remain.</p> <p>Be careful with <code>or</code>. The expression <code>[Status] <> "Closed" or [Status] <> "Cancelled"</code> is true for almost every value: a row cannot normally equal both Closed and Cancelled at once, so at least one not-equal test succeeds. To exclude either value, use <code>and</code>, not <code>or</code>.</p> <p>That logic is a common source of incorrect filters. Translate the condition into plain language before entering it: “keep the row only if it is not Closed and not Cancelled.” Then test one example of each category, including a blank if blanks are possible. A few hand-checked records can reveal a logic error before the query is loaded or refreshed.</p> <h2>Reviewing and maintaining a not-equal step</h2> <p>Power Query records transformations as ordered steps. A not-equal filter is often easier to maintain when its purpose is clear from the step name and surrounding query structure. You can rename an Applied Step by right-clicking it and choosing Rename. Names such as “Remove closed orders” communicate more than a generic label such as “Filtered Rows,” especially in a query with many transformations.</p> <p>Remember that the filter runs whenever the query refreshes. New rows from the source will be tested by the same condition, which is usually the point of using a query rather than manually deleting records. But if the source changes its status labels, capitalization, or data types, the filter may no longer match the intended rule. Recheck the query after a source-system change and validate the row counts after refresh.</p> <p>Excel for the web can refresh and work with many Power Query scenarios, but authoring and editing capabilities can differ from Excel for Windows and vary with the connection and feature. The steps above describe Excel for Microsoft 365 on Windows. On Mac, Power Query support and available connectors or editing features can differ by Excel build; if a particular editor command is unavailable, use a supported Windows version rather than assuming the menus are identical.</p> <h2>Frequently Asked Questions</h2> <p><strong>What is the not-equal symbol in Power Query?</strong><br />The M language uses <code><></code>. For example, <code>[Status] <> "Closed"</code> tests whether the current row’s Status differs from Closed.</p> <p><strong>Is Power Query’s not-equal operator the same as Excel’s?</strong><br />The symbol is the same: <code><></code>. However, Power Query expressions use M syntax, while worksheet formulas use Excel formula syntax and cell references.</p> <p><strong>How do I filter out a value without writing code?</strong><br />Open the column’s filter dropdown, choose the appropriate Text Filters, Number Filters, or date-filter option, and select Does Not Equal. Enter the value to exclude, then inspect the preview to confirm the result.</p> <p><strong>Does not equal include null values?</strong><br />In M, null differs from a non-null value, so a condition such as <code>[Status] <> "Closed"</code> can retain rows where Status is null. Add an explicit null condition if you want to exclude missing values.</p> <p><strong>Why does a not-equal condition keep the wrong rows?</strong><br />Check whether the comparison uses the right column and data type, whether text capitalization varies, and whether null or whitespace-only values are present. Also check the logic: excluding two different values normally requires joining the not-equal conditions with <code>and</code>.</p> <h2>Related</h2> <ul> <li><a href=Power Query Join Tables: A Practical Guide

  • How to Compare Data in Two Excel Sheets Step by Step
  • Revert Text to Columns in Excel: Undo or Rejoin Data

    • Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

    More in How-to basics

    Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

    Spotted an error in this guide? Let us know.