Power QueryExercises

Why is my filter keeping blanks? null, empty text and errors in practice

Troubleshooting · By Tahir Nas · Examples run in our simulator · Published · Updated

Jump to the practice tasks

Symptom → cause

SymptomCauseFix
<> "" filter keeps blank rowsThe blanks are null, and null <> "" is trueTest _ = null or _ = "", or standardise first
Fill Down skips some gapsThose gaps are empty text, not nullReplace "" with null, then fill
Merge misses rows with blank keysnull and "" keys don't match each otherStandardise keys before merging
Group By shows two 'blank' groupsOne group is null, the other ""Standardise before grouping
Full name is null for some people& with a null part returns nullText.Combine, which skips nulls
Total fails with DataFormat.ErrorA cell error from an earlier type changeFind and handle the error rows
Average looks too highMissing values are null and excludedDecide whether missing means 0

See all four at once

This small table shows how each kind of 'blank' answers three common tests:

let
    Cells = {null, "", " ", "x"},
    Label = (v) => if v = null then "null" else """" & v & """",
    Rows = List.Transform(Cells, each {Label(_), _ = null, _ = "", (try Text.Trim(_) otherwise null) = ""})
in
    #table({"Value", "= null", "= """"", "Trim = """""}, Rows)

Result

Value= null= ""Trim = ""
nulltruefalsefalse
""falsetruetrue
" "falsefalsetrue
"x"falsefalsefalse

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

Only a test that trims and checks null catches every blank-looking value. Inside M text, a quote is written twice: """" is a text containing one quote.

One standardising step

Put a single step near the top of the query that turns every blank-looking text value into null. Every later filter, merge and group then sees one kind of blank:

let
    Source = #table({"Supplier", "Region"}, {{"Acme", "North"}, {"", " "}, {null, "South"}, {"  ", ""}}),
    Columns = Table.ColumnNames(Source),
    Blanked = Table.TransformColumns(Source, List.Transform(Columns, (c) => {c, each if _ = null or Text.Trim(_) = "" then null else _, type text}))
in
    Blanked

Result

SupplierRegion
AcmeNorth
nullnull
nullSouth
nullnull

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

Errors are different

An error isn't a blank: it's a failed calculation stored in the cell, and comparing it raises the error again. Handle errors explicitly (remove, replace with null and flag, or keep for review) right after the step that creates them. See find, remove and replace errors.

Practise it in the simulator

Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. Free tasks need no account.

Lessons

More resources

Sources

Microsoft's official documentation for the features and functions used on this page.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.