Principle
Read distinct and unique in Power Query
Two similar words answer different counting questions.
Before you use it
- Use when
- Power Query’s Column distribution shows distinct and unique counts.
Keep this limit in mind
- These are Power Query’s labels; verify other tools’ definitions. A result without duplicates does not prove that excluded records were checked or that the key will remain stable.
An idea to explore.
Try this idea.
Inspect the example, try the idea in your situation, then check your next move.
Why it matters
In Power Query, distinct counts different values. Unique counts values that occur exactly once. Both describe the profiled rows, so a once-seen value in the first 1,000 rows may repeat later. Check the needed scope before using either count to assess a candidate identifier.
Steps
- Set the needed query and profiling scope, then inspect repeated values.
- Check the full identifier if it uses more than one field.
An example
In this example, codes A,A,B,C give three distinct values and two unique values: B and C. A subset containing only A,B,C would show three of each and hide the repeated A.
Check your result
Explain both counts, state the profiling scope and list repeated candidate keys in the records you actually need.
Save and practise this idea
My learning
Read. Recall. Repeat.
Save this idea and return to it on any device.
Keep exploring
Connected ideas
You are readingRead distinct and unique in Power Query
Evidence and sources
Power Query data profiling uses the first 1,000 rows by default and offers entire-dataset profiling. In Column distribution, distinct counts different values; unique counts values occurring once in the profiled column.
Claim limit: Tool-specific documented behavior, not a universal definition of unique or an empirical test of Power Query. Counts from a restricted query or partial profile do not establish source-wide coverage or a stable business identifier. Source reading history: selected_passages; 2026-10-11; Default profiling scope note, Column quality labels and Column distribution definitions. Official page was opened on 2026-10-10 and identified sections inspected. This verifies a narrow reference basis, not the effectiveness or completeness of independently drafted practice steps. On 2026-10-11 those sections were read again for cards 9726/9727. The content was readable despite the page’s generic authorization banner. No application or connector was executed. Agent release review: PostgreSQL 18 table expressions, NULL comparisons and aggregates, the UK Government Data Quality Framework and Microsoft Power Query profiling documentation were checked at the cited sections. The original examples and scope limits remain attached.
Using the data profiling tools - Power Query · Default profiling scope note and Column distribution definitions
All sources (1)
Edition and review
Version 0.1.0 · Published
Edition checked · 2026-11-12