In Excel Power Query, null means that a value is missing or unavailable; it is not the same thing as the text "", a zero, or an error. You can keep nulls, filter them out, or replace them with a suitable value in Power Query Editor, but choose based on what the missing data means rather than changing every null automatically.
What null means in Power Query
Power Query uses the M language to represent the absence of a value as null. When you connect to a worksheet or another source, genuinely empty cells in a column commonly appear as null in the query preview. A database may also return null when a field has no recorded value. The preview displays null explicitly so you can distinguish missing data from ordinary text and numbers.
Null does not tell you why a value is absent. A blank in a delivery-date column could mean an order has not shipped yet, while a blank in a customer-name column could mean the source record is incomplete. Power Query can show and transform the missing value, but the business meaning has to come from the source and your process.
In M, null is a special value, not a word stored as text. For example, a null in a numeric column is still missing data; it is not the number zero. A text field containing the characters null is different again. That is ordinary text and can be searched, joined, or filtered as text.
Null, blank, empty text, zero, and errors
Several things can look blank in a spreadsheet but behave differently after import. Knowing which one you have prevents incorrect replacements and confusing totals.
- Null: No value is present in the field. Power Query often uses this for a genuinely empty source cell.
- Empty text: A text value with zero characters, represented in M as
"". A formula that returns an empty string can look blank in Excel, but its imported representation can depend on the source and connector. - Whitespace: A text value made of spaces or other whitespace. It may look empty but is not necessarily null or empty text. Trimming the text can help reveal it.
- Zero: A real numeric value. Replacing it with null, or null with zero, changes the meaning of calculations.
- Error: A failed operation or invalid value, such as a conversion problem. Errors are not nulls and should be diagnosed or handled separately.
Excel’s display can obscure these distinctions: a formula returning "" may appear to leave a cell blank, just as a truly empty cell does. If the difference matters, inspect the Power Query preview and test the transformation on a few known rows. Do not assume that every visually blank cell came into the query in the same form.
How to locate nulls in a query
In Excel for Microsoft 365 on Windows, open the workbook’s query from Data > Queries & Connections, then double-click it to open Power Query Editor. In the editor, look at the column’s filter menu or scan the preview for cells displaying null. A filter menu commonly lists null as a selectable item when that column contains null values. Selecting it lets you inspect only those rows; clear or change the filter afterward if you want the full dataset back.
Column profiling can also help when a table is too large to inspect by eye. In Power Query Editor, use the View tab’s data profiling options, such as Column quality, to review valid, error, and empty values. Check the profiling indicator’s scope: depending on the setting, the profile may describe only the preview rather than every row in the query. For important decisions, verify the result against the full data or a known sample.
To find out whether a value is null or merely text that looks blank, select a few affected rows and inspect the column’s values and type. Filtering for null will not necessarily find empty strings or strings made of spaces. Those need their own tests or cleanup steps.
Ways to handle null values
There is no universal best replacement. Keep nulls when missingness matters, remove rows when they cannot be used, or fill them only when a defensible rule exists. Make the choice explicit in the query so it is applied again consistently at refresh.
- Keep them: Preserve nulls when the value is genuinely unknown or not yet available. For example, an unshipped order may have no ship date yet.
- Filter them out: Exclude rows where a required field is missing, such as a transaction with no reliable transaction ID. Filter only the necessary column and consider whether excluding the row would hide useful information.
- Replace them with a known default: Use a value such as zero only when the field’s definition supports that interpretation. A missing sales amount is not automatically a zero sale.
- Use a label for text: A label such as “Not provided” can make reports easier to read, but it turns the field into text and may affect sorting, grouping, or later type conversion.
- Fill from related records: Sometimes a missing value can be supplied from another column or a trusted lookup table. Record the rule and check that the match is unambiguous.
For a simple UI-based transformation, select the column and look for Replace Values on the Transform tab. Because null is a special value, not the text “null,” verify the preview after entering a replacement. If the dialog treats the input as text or the result is unclear, use a clearly defined M step instead. To exclude nulls, filter the column and deselect the null entry; inspect the resulting row count before loading.
Replace nulls with Power Query M
When the interface does not make the intended operation clear, M gives you a precise, repeatable transformation. In the following examples, Source is the name of the previous query step. Use the Advanced Editor or the formula bar to add or adapt a step, taking care to preserve the query’s existing step names and syntax.
To replace nulls in a numeric column named Amount with zero, use:
Table.ReplaceValue(Source, null, 0, Replacer.ReplaceValue, {"Amount"})
This replaces only actual null values in Amount; it does not replace zeroes or the text “null.” The column should have a suitable numeric type. If zero would falsely imply a measured zero amount, do not use this replacement.
For a conditional replacement in a custom step or column, the M expression is:
if [Amount] = null then 0 else [Amount]
For a text column named Comment, this expression changes empty text to null:
if [Comment] = "" then null else [Comment]
That last expression does not remove spaces from a value such as . If whitespace is the issue, trim text as a separate, deliberate cleanup step, then check how the connector handles nulls and empty strings. Do not apply text operations to a column that contains non-text values without first considering its type.
These are Power Query M expressions, not worksheet formulas. M uses null for the missing value and curly braces for a list of column names. Keep the column type in mind: text labels, numbers, and dates are not interchangeable, and a replacement must be compatible with the later steps in the query.
Deciding whether to replace or preserve nulls
Before changing missing values, ask what the field represents, how the source creates missing entries, and what downstream users expect. A default is justified only when it communicates the intended meaning. For example, a missing optional middle name might appropriately remain null, while an absent category might be grouped under an explicitly named “Not provided” label for a report.
Think about the effect on analysis as well as appearance. Replacing missing costs with zero can lower an average; replacing unknown dates with a fixed date can distort timelines; and deleting every row with any null can discard records that are complete in all the fields you actually need. If you do filter rows, write down the criterion and compare the row count before and after filtering.
For recurring work, make the transformation in Power Query rather than manually editing the loaded worksheet. Query steps are reapplied when you refresh, whereas manual edits to the output can be overwritten or become inconsistent with the source. Keep the original source data intact where possible, and use a separate query step or reference query for cleaned data.
Common null-value problems
“Replace null” did not change anything. The cells may contain empty text, spaces, or the literal text null rather than a null value. Filter or inspect the source values first. For a robust rule, handle each case separately instead of treating every visually blank value as identical.
A replacement causes a type error. A column typed as a number cannot reliably contain a text label such as “Missing.” Decide whether the column should remain numeric with nulls, use a numeric default that is meaningful, or be converted to text for a presentation field. Check the Changed Type step, since an earlier or later type conversion can also create errors.
Rows disappear after cleaning. A filter step may be excluding nulls, or a later join or transformation may be affecting the rows. Review Applied Steps in order and select each step to see where the row count changes. Filtering a required identifier is different from filtering an optional note.
Nulls return after refresh. Refresh reruns the query against its source. If the source still has missing values, they can appear again unless the query includes a transformation to handle them. Conversely, if a source is corrected, a query that preserves nulls can reflect that updated data on refresh.
A formula that looks blank behaves unexpectedly. Excel formulas returning an empty string can be visually indistinguishable from empty cells. Confirm the value Power Query receives before building a replacement rule. A null filter and an empty-text test target different cases.
Loading and refreshing the cleaned data
After making changes, review the preview and the Applied Steps list, then choose Close & Load to return the result to Excel. You can also use Close & Load To when you need to select a destination or load the query as a connection. The exact options available depend on the query and workbook setup. If another query depends on this one, check that its expected column names and data types still match.
When a workbook is refreshed, Power Query reruns its steps rather than treating the current output table as the permanent source of truth. That is why a null-handling rule belongs in the query when it should apply each time. After refresh, check representative rows, column types, and totals if the replacement or filtering affects reporting. On Excel for Mac or Excel for the web, Power Query availability and editing features can differ from Microsoft 365 for Windows; the Windows ribbon paths above may not match, so use the query editor and refresh options available in that version.
Frequently Asked Questions
Is null the same as blank in Excel Power Query?
Not always. A truly empty source cell commonly becomes null, but a formula returning an empty string or a cell containing spaces may be represented differently. Inspect the imported value before choosing a filter or replacement.
How do I remove rows with null values?
Open the column’s filter menu in Power Query Editor and deselect the null entry. This removes rows with null in that column, not necessarily rows containing a null anywhere in the table. Check the row count and whether the chosen column is required.
Can I replace null with zero?
Yes, but only when zero is a truthful default for that field. Use Replace Values or an M step such as Table.ReplaceValue(Source, null, 0, Replacer.ReplaceValue, {"Amount"}) for a numeric Amount column. Do not use zero to disguise unknown measurements.
Why does Power Query show null after I refresh?
The refreshed source may still contain missing values, or the source may have changed. A query that preserves nulls will show them again. Add a documented replacement or filter step only if the intended treatment should apply on every refresh.
Does null mean an error?
No. Null means a value is missing; an error means a value or operation could not be evaluated successfully. Filtering or replacing nulls does not automatically resolve errors, so inspect and handle error values separately.
Related
- Excel Power Query Connection Only Explained
- Stampin’ Up Inventory Spreadsheet: What to Track
- Spreadsheet Free Budget Planner: A Simple Excel Setup
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Outlook Distribution Lists: Create and Manage Large ListsYes. In classic Outlook for Windows, go to People, choose Home > New Contact Group, add members, and…
- How to Delete a Page in Microsoft Word Without Losing TextTo delete a page in Microsoft Word, select the page’s contents and press Delete, or remove the page…
- Company PowerPoint Template: Build a Reusable DeckTo create a company PowerPoint template, set up the brand colors, fonts, and reusable slide layouts in Slide…
- How to Create a Real Handwritten Signature in OutlookTo create a real handwritten signature in Outlook, write your name on paper, save a clear photo or…
- Excel Page Break Reset: How to Restore DefaultsTo reset page breaks in Excel for Microsoft 365 on Windows, select the worksheet, then choose Page Layout…
- How to Set Out of Office Hours in Outlook: Desktop & WebTo set out-of-office hours in Outlook, open Automatic replies, turn on automatic replies, choose the start and end…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.