Power QueryExercises

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

ExpressionResult
null + 1null
null = nulltrue
null = ""false
Text.Length("")0
"a" & nullnull
Number.FromText("n/a")error

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Inside M text, a double quote is written twice: """" 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
    NotEmpty

Result

SKUSupplier
S-1Acme
S-2null
S-4Brightline

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
    Standard

Result

SKUSupplier
S-1Acme
S-2Unknown
S-3Unknown
S-4Brightline

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The null test comes first: 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.