2.2.1. Evaluating Data with Column Statistics and Properties
💡 First Principle: Each profiling view answers a different question about a column: Is it valid? (quality), How varied is it? (distribution), and What does it actually contain? (profile). Turning on all three from the View ribbon gives you a quick check of any column before you trust it.
| Tool (View ribbon) | Shows | Use it to spot |
|---|---|---|
| Column quality | Percent Valid, Error, Empty under each header | Type conversion errors, missing values |
| Column distribution | Mini histogram plus distinct and unique counts | Unexpected duplicates in a key, low or high variety |
| Column profile | Full statistics pane (count, error, empty, distinct, unique, min, max, average, standard deviation, NaN, zero) and value distribution chart for the selected column | Outliers, invalid ranges, skew |
Two definitions cause frequent exam errors. Distinct counts how many different values appear at all; unique counts how many values appear exactly once. A Country column with values USA, USA, Canada, Mexico has 3 distinct values and 2 unique values (Canada and Mexico). For a column meant to be a dimension key, distinct and unique should both equal the row count; if unique is lower, the key has duplicates and can't sit on the one side of a relationship.
Empty in column quality means null or empty values. Power Query distinguishes null (no value) from an empty string "", and they behave differently in replacements and merges, so profile before you choose a cleaning step. Column properties worth checking at this stage include the data type icon in the header (ABC for text, 123 for whole number, 1.2 for decimal, the calendar icon for dates, ABC/123 for an untyped "any" column), because an untyped or mistyped column is the most common source of downstream errors.
Remember the scope switch: profiling runs on the top 1,000 rows by default. For a final check before publishing, switch to the entire data set (status bar at the bottom of the editor), accepting that it takes longer on large sources.
⚠️ Exam Trap: "A column shows 100% valid in column quality, but refresh fails with type errors" is the 1,000-row default at work: the bad rows are further down. The answer is profiling the entire data set, not assuming the source changed.
Reflection Question: An OrderID column shows 10,000 distinct and 9,950 unique values across 10,000 rows. What does that tell you about the column, and what would happen if you tried to use it as the one side of a relationship?
