2.2.2. Resolving Inconsistencies, Null Values, and Data Quality Issues

💡 First Principle: Cleaning is about making equal things look equal and missing things explicit. Every cleaning step either standardizes values (so the engine groups them correctly) or decides what a missing value should mean (so aggregations don't silently drop or distort it).

The Power Query toolkit for common issues:

• Inconsistent text: Trim removes leading and trailing spaces; Clean removes non-printable characters; Format > lowercase / UPPERCASE / Capitalize Each Word standardizes casing; Replace Values fixes known variants ("N.E." becomes "Northeast"). Power Query comparisons are case-sensitive, so "northeast" and "Northeast" are different values to Power Query, even though the model's VertiPaq engine is case-insensitive and will later merge them into one. Standardize casing before grouping or removing duplicates.
• Missing values: Replace Values can replace null with a default (0 for quantities only when zero is truly correct, "Unknown" for categories). Fill Down and Fill Up copy the nearest non-null value into blanks, which is the right fix for reports where a category label appears only on the first row of each group. Remove Rows > Remove Blank Rows deletes rows where every column is empty.
• Duplicates: Remove Duplicates on selected columns keeps the first occurrence. On a dimension, apply it to the key column to guarantee uniqueness, after trimming and standardizing case so near-duplicates are caught.
• Unexpected values: Filter rows to exclude test records or impossible values (negative ages, future birth dates); Conditional Column maps ranges or codes into clean categories.
• Structural noise: Remove Top Rows / Remove Bottom Rows strip report titles and footers from exported files; Use First Row as Headers promotes the real header row.

Deciding how to treat nulls is a business decision, not a technical one. Replacing a null sales amount with 0 says "we sold nothing," which may be false; leaving it null excludes the row from averages. When unsure, keep nulls and surface them (a count of rows with missing values) rather than inventing data.

⚠️ Exam Trap: Remove Duplicates in Power Query is case-sensitive, but model relationships and VertiPaq aren't. A dimension key containing "ABC" and "abc" survives Remove Duplicates, then fails when loaded as the one side of a relationship because the model sees duplicates. Standardize case (Format > UPPERCASE) before removing duplicates.

Reflection Question: A Region column in an exported report shows a value only on the first row of each block, with blank cells below it. Which single transformation fixes it, and why is Replace Values with a constant the wrong choice?