Advanced Cleaning Techniques

Basic cleaning removes blank rows and fixes column names. Advanced cleaning handles the subtler problems — cell-level errors that break type changes, null values that silently corrupt calculations, inconsistent text that prevents accurate grouping, and source data that is messy in different ways every time it arrives. This lesson gives you the complete toolkit for making Power Query queries that are robust under real-world conditions.

Understanding Cell-Level Errors

An error at the cell level is different from a step-level error. A step-level error breaks the entire query — nothing loads. A cell-level error is contained to one cell in one row — the rest of the column loads fine, but that cell shows the word "Error" in red. Both need to be handled, but in different ways.

To see the exact error message for any red cell, click the cell in the preview. Power Query shows the error type and message below the grid. The three most common causes of cell errors are type conversion failures, division by zero, and out-of-range values.

Three Ways to Handle Cell Errors

Option 1 — Replace Errors

Replace Errors substitutes a fixed value for every error in the selected column. The replacement value becomes part of the loaded data — DAX measures will use this value in calculations. Choose null when you want errors to be ignored by aggregations, or 0 when you need a numeric placeholder.

Option 2 — Remove Errors

Remove Errors deletes the entire row for any row that contains an error in the selected column. Use this when error rows are genuinely invalid and should not be included in any calculation — for example, rows where the primary key is an error and the row is meaningless without it.

Option 3 — Fix the Source of Errors

The cleanest approach is to fix the root cause before the error occurs. If Revenue contains "N/A" strings that fail type conversion, add a Replace Values step before the Changed Type step — replace "N/A" with null first, then change the type. The type change now succeeds because null converts cleanly to any type.

// Robust pattern — fix before the type change, not after

// Step 1 — Replace Values: clean known bad text values first
// Transform tab → Replace Values
#"Replaced N/A" = Table.ReplaceValue(
    #"Promoted Headers",
    "N/A", null,          // find "N/A", replace with null
    Replacer.ReplaceValue,
    {"Revenue"}           // in the Revenue column only
),

// Step 2 — Replace Values: clean currency symbols
#"Removed Dollar Sign" = Table.ReplaceValue(
    #"Replaced N/A",
    "$", "",              // find "$", replace with empty string
    Replacer.ReplaceText, // ReplaceText for partial matches
    {"Revenue"}
),

// Step 3 — Remove commas from number formatting
#"Removed Comma" = Table.ReplaceValue(
    #"Removed Dollar Sign",
    ",", "",
    Replacer.ReplaceText,
    {"Revenue"}
),

// Step 4 — Now change type safely — no errors will occur
#"Changed Type" = Table.TransformColumnTypes(
    #"Removed Comma",
    {{"Revenue", type number}}
)

Handling Null Values

Null is Power Query's representation of a missing or unknown value. It is not zero, not an empty string, and not the word "null" — it is the complete absence of a value. Nulls behave differently depending on context, and understanding those differences prevents silent calculation errors.

Filling Nulls — Fill Down and Fill Up

A common pattern in exported spreadsheets is a merged cell that was exported as one value followed by nulls. The Department column might say "Sales" in the first row of a group, then null for all subsequent rows in that group. Fill Down replaces each null with the value from the row above — reconstructing the implied grouping.

Standardising Inconsistent Text

Inconsistent text in dimension columns silently breaks grouping, relationships, and filters. "New York", "new york", "NEW YORK", and "New York " (trailing space) are four different values in Power Query — they will not group together and will not match across tables. Fixing text consistency is one of the highest-impact cleaning steps you can do.

Column Quality — Finding Problems Fast

Before writing any cleaning steps, turn on Column Quality in the View tab. It shows a mini data quality bar below each column header — the percentage of values that are valid, in error, or empty. This gives you an immediate picture of which columns need attention and how severe the problems are.

A Complete Robust Cleaning Template

// Robust cleaning sequence — apply to every new data source

// 1. Fix structure first
#"Removed Top Rows"    = Table.Skip(Source, 2),
#"Promoted Headers"    = Table.PromoteHeaders(#"Removed Top Rows", ...),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Promoted Headers", 1),

// 2. Clean text columns BEFORE type changes
//    Trim removes leading/trailing spaces
//    Clean removes non-printable characters
#"Trimmed Text" = Table.TransformColumns(
    #"Removed Bottom Rows",
    {
        {"CustomerName", Text.Trim},
        {"City",         Text.Trim},
        {"Status",       Text.Trim}
    }
),
#"Cleaned Text" = Table.TransformColumns(
    #"Trimmed Text",
    {
        {"CustomerName", Text.Clean},
        {"City",         Text.Clean},
        {"Status",       Text.Clean}
    }
),

// 3. Standardise case
#"Proper Case" = Table.TransformColumns(
    #"Cleaned Text",
    {
        {"CustomerName", Text.Proper},
        {"City",         Text.Proper},
        {"Status",       Text.Proper}
    }
),

// 4. Replace known bad values before type changes
#"Replaced N/A"      = Table.ReplaceValue(#"Proper Case", "N/A", null, Replacer.ReplaceValue, {"Revenue"}),
#"Removed Dollar"    = Table.ReplaceValue(#"Replaced N/A", "$", "", Replacer.ReplaceText, {"Revenue"}),
#"Removed Comma"     = Table.ReplaceValue(#"Removed Dollar", ",", "", Replacer.ReplaceText, {"Revenue"}),

// 5. Now set types safely
#"Changed Type" = Table.TransformColumnTypes(
    #"Removed Comma",
    {{"OrderID", Int64.Type}, {"OrderDate", type date},
     {"Revenue", type number}, {"CustomerName", type text}}
),

// 6. Handle any remaining errors after type change
#"Replaced Errors" = Table.ReplaceErrorValues(
    #"Changed Type",
    {{"Revenue", null}}   // replace any remaining Revenue errors with null
),

// 7. Fill down merged-cell nulls
#"Filled Down Department" = Table.FillDown(#"Replaced Errors", {"Department"}),

// 8. Remove rows where key columns are null
#"Removed Null Orders" = Table.SelectRows(
    #"Filled Down Department",
    each [OrderID] <> null
)

Practice

Lesson Quiz

Next up — Lesson 24 covers Replacing Errors and Handling Exceptions — a deeper look at error detection, conditional error handling with try…otherwise in M code, and building queries that recover gracefully when source data is unpredictable.