Why is my filter keeping blanks? null, empty text and errors in practice
Troubleshooting · By Tahir Nas · Examples run in our simulator · Published · Updated
Symptom → cause
| Symptom | Cause | Fix |
|---|---|---|
<> "" filter keeps blank rows | The blanks are null, and null <> "" is true | Test _ = null or _ = "", or standardise first |
| Fill Down skips some gaps | Those gaps are empty text, not null | Replace "" with null, then fill |
| Merge misses rows with blank keys | null and "" keys don't match each other | Standardise keys before merging |
| Group By shows two 'blank' groups | One group is null, the other "" | Standardise before grouping |
| Full name is null for some people | & with a null part returns null | Text.Combine, which skips nulls |
| Total fails with DataFormat.Error | A cell error from an earlier type change | Find and handle the error rows |
| Average looks too high | Missing values are null and excluded | Decide 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 = "" |
|---|---|---|---|
| null | true | false | false |
| "" | false | true | true |
| " " | false | false | true |
| "x" | false | false | false |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
"""" 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
BlankedResult
| Supplier | Region |
|---|---|
| Acme | North |
| null | null |
| null | South |
| null | null |
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.
- 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~8 minPro
Replace bad quantities with null and flag them (kite shop)
Keep every order, but turn unreadable quantities into null and add a Needs Review flag.
Related pages
Lessons
Sources
Microsoft's official documentation for the features and functions used on this page.
- Dealing with errors in Power Query (Concept, Microsoft Learn)
- Replace values and errors (Concept, Microsoft Learn)
- Using the data profiling tools (Concept, Microsoft Learn)
- List.Transform (Function reference, Microsoft Learn)
- Text.Trim (Function reference, Microsoft Learn)
- Table.ColumnNames (Function reference, Microsoft Learn)
- Table.TransformColumns (Function reference, Microsoft Learn)
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.