2.2. Profile and Clean the Data

💡 First Principle: You can't fix what you haven't measured. Profiling turns vague suspicion ("the data seems off") into specific facts ("4% of CustomerID values are empty, and Region has seven spellings of 'Northeast'"), and those facts tell you exactly which cleaning step to apply.

Why care? Dirty data doesn't announce itself in a report; it produces confident, wrong numbers. A null quantity silently drops out of a sum; "NorthEast" and "Northeast" split one region into two bars; a text value in a numeric column fails the whole refresh. The exam tests both halves of the discipline: reading Power Query's profiling tools correctly, and choosing the cleaning action that fixes the issue without losing good data.

Think of profiling as a health check-up before surgery. The check-up (column quality, distribution, profile) is cheap and non-invasive; the treatment (replace, fill, remove) changes the patient, so you want an accurate diagnosis first.

⚠️ Common Misconception: Profiling statistics describe the entire table. By default, Power Query computes column quality, distribution, and profile on the first 1,000 rows only. A problem that starts at row 50,000 is invisible until you switch the status bar setting to Column profiling based on entire data set.