Excel Power Query: How to Keep Null Values in Data

Yes. Excel Power Query keeps null values unless a query step filters them out, replaces them, or changes them through another transformation. To preserve them, avoid excluding the null entry in a column filter; if you need to keep only rows where a particular column is null, filter that column specifically for null.

What null means in Power Query

A null represents missing or absent data in Power Query. It is not a numeric zero, a space, or necessarily the same thing as an empty text value. This difference matters because a query can treat those cases differently when filtering, grouping, changing types, or loading results.

When Power Query reads a genuinely empty cell from an Excel range or table, it commonly represents that cell as null. A formula that returns an empty string, such as a formula whose result is "", may instead arrive as empty text. A cell containing one or more spaces is text too, not null. The source and connector can affect what appears, so inspect the value in the Power Query Editor rather than assuming that every cell that looks blank has the same underlying value.

Null is also different from an error. An error is a failed value, such as text that could not be converted to a number. Keeping nulls does not automatically keep errors, and removing errors does not necessarily remove nulls. If both kinds of rows matter, check each case separately.

Null: a missing value, shown as null in the editor preview.
Empty text: text with zero characters, often produced by a formula returning "".
Whitespace: text containing spaces or other characters that may be hard to see.
Error: a value Power Query could not calculate or convert successfully.

Keep nulls when filtering in the editor

In Excel for Microsoft 365 on Windows, open the query from Data > Queries & Connections, then double-click its name to open the Power Query Editor. In the column you are filtering, open the filter dropdown. The list of values may include a null entry, often displayed as (null) or null, depending on the interface and data. Leave that entry selected if rows containing null should remain in the results.

A filter is a query step, not just a temporary display setting. If you deselect null and apply the filter, Power Query adds a step that removes rows whose value in that column is null. To undo that change, use the query’s Applied Steps pane: select the filter step and remove it, or edit the column’s filter dropdown and select null again. Removing a filter step restores rows only if no earlier step has already removed them from the source.

To return only rows where one field is missing, do the opposite: clear the other values in that field’s filter list and keep only null selected. This is useful for finding incomplete records, such as orders with no ship date. It does not mean that Power Query is globally set to keep or remove nulls; the result depends on the specific filter step and column.

Keep null rows while filtering for other values

A common surprise occurs when a reader filters a column to show a desired value, such as “Approved.” The resulting rows with null in that column disappear because they do not meet the filter condition. If you want approved rows and rows where the status is missing, the filter must explicitly allow both cases. In the filter menu, keep both Approved and null selected when those are the only values needed.

For more complex conditions, use the filter menu’s text or number filters where appropriate, or edit the M expression in the formula bar or Advanced Editor. The following example keeps rows where Status is null or equals Pending. It assumes the preceding query step is named Source and the column is named Status:

Table.SelectRows(Source, each [Status] = null or [Status] = "Pending")

In a full query, that expression belongs in a step, typically after the source step. If you edit an existing query, use its actual preceding step name and column name; Power Query step names with spaces or punctuation use a quoted form such as #"Changed Type". Keep the null check in the condition when the intent is to preserve missing statuses alongside selected statuses.

Be careful with a condition that only tests whether a value is not equal to a particular word. It may not express the intended treatment of missing data. For a clear result, state the null case explicitly when it must be retained, then test the other values you want. That makes the logic easier to review later.

Check the M code and Applied Steps

Power Query records transformations as steps written in the M language. A filter step can remove nulls even if you no longer remember choosing that option. In the Editor, review Applied Steps from top to bottom. Select a step to see its effect and, when available, inspect the formula bar. A step named Filtered Rows is a common place to look, although step names can be changed and are not proof of what the step does.

For example, this M expression selects only rows whose Status value is null:

Table.SelectRows(Source, each [Status] = null)

By contrast, a filter step that selects only a non-null value will discard null rows in that column. If the query has many transformations, inspect the result after each step to identify exactly where the rows disappear. This is more reliable than changing several steps at once, especially when a later step depends on a column or value created earlier.

You can also look for steps that replace values, remove rows, or fill values. A step may have a generic name, so the formula bar or Advanced Editor can help confirm its actual operation. In the Advanced Editor, make a copy of the query or record the original expression before editing; a small change to step names or references can prevent the query from refreshing.

Transformations that change or remove nulls

Keeping nulls at the filter stage does not guarantee they will remain null throughout every transformation. Some operations intentionally alter them, while others remove whole rows. Check what each step means for the column whose missing values matter.

Remove Rows: A filter or remove-rows operation can discard rows containing nulls. Remove Blank Rows generally targets rows that are blank across their fields; it is not the same as removing every row where one particular column is null.
Replace Values: Replacing null with a label, zero, or another value changes the meaning of the data. Use this only when the replacement is justified, not simply to make the preview look complete.
Fill Down or Fill Up: These operations propagate neighboring values into null cells. They can be appropriate for grouped reports where a label is visually repeated only once, but can be misleading for records where a blank means unknown.
Change Type: A type conversion may leave a null as null, but invalid non-null text can produce errors. Review errors separately and do not assume a conversion has repaired missing values.
Merge or expand: A left outer merge can produce nulls where no matching record exists. Filtering those results may effectively turn the output into a match-only result, so decide whether unmatched records should stay.

For example, if a customer table is left-joined to a delivery table, a null delivery date may mean that no delivery record matched. Removing every row with a null delivery date would also remove those customers from the output. That may be correct for a delivery-only report, but it is not correct if the goal is to retain every customer and flag missing deliveries.

Load and refresh without losing missing values

When the query is ready, choose Home > Close & Load or Home > Close & Load To in the Power Query Editor. A null may appear as a blank cell after loading to an Excel worksheet. That appearance alone does not show whether it is still a null or was converted to empty text, so check the query preview or the transformation steps if the distinction matters.

After loading, refresh the query using Data > Refresh All or the refresh command for the query. A refresh reruns the recorded steps against the current source; it does not remember a manual correction made only in the worksheet output. If new source rows are missing after refresh, inspect the query’s filters and steps rather than editing the loaded table, because the next refresh can overwrite changes made directly to query output.

Power Query authoring and connector support can vary by Excel platform and build. The steps above describe Excel for Microsoft 365 on Windows. Excel for Mac may present different commands or support a different set of connectors and editing features; use the Power Query Editor available in your build and verify that its filter retains the null entry. Excel for the web does not provide the same full desktop authoring experience, so use desktop Excel when you need to inspect or edit M steps.

Common problems and how to diagnose them

If rows seem to vanish, identify the point where they disappear rather than assuming that Power Query removed all nulls automatically. Select each Applied Step in order and check the preview. If the row exists before a filter and disappears afterward, inspect that filter’s selected values or M condition. If it is present after the filter but absent later, review subsequent remove, merge, or grouping steps.

The cell looks blank but does not filter as null: It may contain an empty string or spaces. Check the source value and consider whether trimming text or replacing empty text is appropriate. Do not treat spaces as null without deciding that they mean missing data.
Only some rows disappear: A filter on one column removes rows based on that column, not because another column is null. Check the specific field named in the filter step.
Nulls become errors: Look for a type conversion or calculation applied to a non-null value that is invalid. Use the error details to diagnose that value; null handling alone will not fix it.
Nulls return after refresh: The source may have changed, or a step may be replacing or deriving values differently on refresh. Compare the refreshed preview with the source and review the step sequence.
Worksheet blanks seem ambiguous: The loaded result may visually render null and empty text similarly. Validate in the Editor or add a temporary diagnostic transformation rather than relying only on how the worksheet cell looks.

A useful habit is to keep the source step intact and make transformations explicit. Name steps for their purpose, such as “Keep missing statuses,” and avoid replacing nulls merely to silence a visual blank. A clear query is easier to audit when someone later needs to know whether a missing value means unknown, not applicable, or not yet supplied.

Frequently Asked Questions

Does Power Query remove null values by default?
No. Nulls remain unless a filter or another transformation removes or changes them. A filter that selects particular values can exclude nulls if null is not among the selected values.

How do I show only rows with null values?
Open the filter dropdown for the relevant column and select only the null entry. The query will keep rows where that column is null and exclude rows with other values.

Are null and blank the same in Power Query?
Not always. A truly empty source cell is commonly read as null, while a formula result of empty text or a cell containing spaces can be a different value. Check the Editor preview and source when the distinction matters.

Why do my nulls disappear after I apply a filter?
The filter likely selects values that do not include null. Edit the filter to include the null entry, or make the condition explicitly allow both null and the other values you want to keep.

Will null values show as the word null in Excel?
Not necessarily. When loaded to a worksheet, null commonly appears as a blank cell. The worksheet display may not distinguish null from empty text, so verify the value in the Power Query Editor if needed.

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.