Search code examples
excelpowerbipowerquerypowerbi-desktopm

What is the difference between distinct and unique in Power query?

In sql distinct (on column - select count(column) from table) means rows excluding null, duplicates and non-duplicates increment the counter by 1.

Where as distinct (on * - select count(*) from table) means rows including null, and increment logic is same as above.

In Power bi, distinct seems to be same as sql's distinct on count(*) logic. And unique means count of values that do not repeat. Is that correct?

Do both consider nulls and blanks?

Solution

  • Unique are the number of values which appear only once.

    Distinct are the number of individual values excluding any duplicates.

    e.g.

    a
b
b
c
c

    One unique (a) and three distinct (a, b, c)

    UPDATE

    Below shows how nulls and blanks are treated.

    enter image description here

    5 distinct and 3 unique (nulls and blanks are reported as Empty or Empty String as below)

    enter image description here