null, empty text and errors are different things
Recognise and handle null, empty text and error values deliberately.
~9 min read · beginner · Module 3: Data types, locale and nulls
By Tahir Nas · Examples run in our simulator · Published · Updated
Before this: Choose types deliberately: identifiers are text
Three different things can look like an empty cell, and they behave differently in every step that touches them. null means no value. Empty text "" is a text value with zero characters. An error is a failed calculation stored in the cell.
How they behave
#table({"Expression", "Result"}, {
{"null + 1", null + 1},
{"null = null", null = null},
{"null = """"", null = ""},
{"Text.Length("""")", Text.Length("")},
{"""a"" & null", "a" & null},
{"Number.FromText(""n/a"")", try Number.FromText("n/a") otherwise "error"}
})Result
| Expression | Result |
|---|---|
| null + 1 | null |
| null = null | true |
| null = "" | false |
| Text.Length("") | 0 |
| "a" & null | null |
| Number.FromText("n/a") | error |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
"""" is a one-character text containing ".Two results surprise people. Arithmetic and text concatenation with null give null, not 0 or the other text. And null isn't equal to empty text, so a test for one misses the other.
The filter that keeps blanks
A filter like [Supplier] <> "" is meant to drop missing suppliers. It drops only the empty-text row; the null row survives, because null <> "" is true:
let
Source = #table({"SKU", "Supplier"}, {{"S-1", "Acme"}, {"S-2", null}, {"S-3", ""}, {"S-4", "Brightline"}}),
NotEmpty = Table.SelectRows(Source, each [Supplier] <> "")
in
NotEmptyResult
| SKU | Supplier |
|---|---|
| S-1 | Acme |
| S-2 | null |
| S-4 | Brightline |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Standardise explicitly
Decide on one representation for 'missing', and convert the others to it early in the query. Here both null and empty text become "Unknown" for a report:
let
Source = #table({"SKU", "Supplier"}, {{"S-1", "Acme"}, {"S-2", null}, {"S-3", ""}, {"S-4", "Brightline"}}),
Standard = Table.TransformColumns(Source, {{"Supplier", each if _ = null or Text.Trim(_) = "" then "Unknown" else _, type text}})
in
StandardResult
| SKU | Supplier |
|---|---|
| S-1 | Acme |
| S-2 | Unknown |
| S-3 | Unknown |
| S-4 | Brightline |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Text.Trim(null) returns null, and null = "" is false, so the order keeps the logic easy to read.For analysis tables, null is usually the better choice than a placeholder word: list functions such as List.Sum and List.Average skip nulls, and a DAX or PivotTable blank behaves sensibly. Use a label like Unknown only for display columns.
Where to find this in Excel
- Excel for Windows
- Column header filter arrow > Remove Empty
- What you see
- Remove Empty adds one filter step that drops both null and blank values.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Filter by values in a column (Microsoft Learn).
Common mistakes
- Filtering with
<> ""and expecting null rows to disappear. - Replacing null with 0 in a measurement column, which turns 'unknown' into a real-looking value.
- Testing
Text.Trim(_) = ""without handling null first, then wondering why nulls slip through.
Excel, Power BI and the simulator
- Both hosts show null as the word null in the preview and empty text as a blank cell; errors show as Error and can be clicked to see the message.
- Excel worksheets and the Power BI model both turn null into an empty cell or BLANK() after loading, so the difference is only visible inside Power Query. Clean it up there.
- The simulator follows the same rules; when a type conversion meets empty or whitespace-only text it produces null (see the compatibility page, KD05).
Next step
Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's free, with no account needed.
Practise: Treat empty text and null the same (hardware)Practice this
Each exercise runs in the browser and is checked against fresh data, not just the example.
- intermediate~7 minFree
Treat empty text and null the same (hardware)
Some suppliers are null and some are empty text. Standardise both to "Unknown".
- intermediate~7 minPro
Treat empty text and null the same (pharmacy)
Some suppliers are null and some are empty text. Standardise both to "Unknown".
- intermediate~7 minPro
Treat empty text and null the same (cafe)
Some suppliers are null and some are empty text. Standardise both to "Unknown".
- intermediate~8 minFree
Predict which orders a filter will drop, nulls included (kite shop)
Before a Qty ≥ 3 filter goes live, list exactly the orders it will remove, including the ones with no quantity.
- intermediate~8 minPro
Predict which orders a filter will drop, nulls included (bakery)
Before a Qty ≥ 3 filter goes live, list exactly the orders it will remove, including the ones with no quantity.
- intermediate~8 minPro
Predict which orders a filter will drop, nulls included (bike rental)
Before a Qty ≥ 3 filter goes live, list exactly the orders it will remove, including the ones with no quantity.
- beginner~7 minFree
Convert Yes/No answers to true, false or null (charity)
The marketing opt-in column mixes Yes, Y, no, N, blanks and nulls. Turn it into a proper true/false column.
- beginner~7 minPro
Convert Yes/No answers to true, false or null (gym)
The marketing opt-in column mixes Yes, Y, no, N, blanks and nulls. Turn it into a proper true/false column.