Module 2 Lesson 4. Unique vs Distinct
"Unique" and "Distinct" sound like synonyms but mean different things in data work. Understanding the difference helps you choose the right operation when assessing data quality.
Consider a list of customer IDs: A, B, B, C, C, C, D.
Distinct values — every value that appears, with duplicates collapsed: A, B, C, D (4 values).
Unique values — values that appear exactly once: A, D (2 values).
In other words:
Distinct = "what are all the different values?"
Unique = "which values are not repeated at all?"
Column Distribution in Power Query shows both numbers: distinct and unique.
Remove Duplicates keeps the distinct set (one occurrence of each value).
DAX has DISTINCT() and VALUES() functions that return distinct lists.
When you find duplicates, ask why they exist before deleting:
Genuine duplicates (the same row entered twice) → remove.
"Duplicates" caused by case or whitespace (Smith vs smith ) → clean first, then dedupe.
Legitimate repetition (a customer with two orders) → keep; the duplication is in the key, not the data.
A high distinct count on a key column is good — it confirms the column is selective.
A low unique count with a high distinct count means most values repeat — typical for a dimension column like Region or Category.
A high % empty signals missing data that may need a default or a join to a lookup table.
Compare unique vs distinct customer records:
In Power Query, open the column profile on a Customer column.
Note the Distinct and Unique counts.
Apply Remove Duplicates and observe how row count drops to the distinct count.
Discuss with a colleague: would removing duplicates here be correct, or does it lose information?