Difference between Distinct and Unique when using column distribution

[mario_ruiz]

When using the Transform Data feature, over the View tab, there's an option that we can check: Column distribution, which brings distinct and unique values... What's the difference between these values? By Distinct values can I assume there are duplicates of the same value and for unique there are no duplicates? I already went into the documentation but it still is not clear. Thanks. 

https://docs.microsoft.com/en-us/power-query/data-profiling-tools

[AbhinavJoshi]

Hello, does distinct calculates null too? 

[stumit]

A good question given that the link that mario_ruiz's description of the "definitive" MicroDoc he linked to falls short.
The page https://docs.microsoft.com/en-us/power-query/data-profiling-tools mentions the terms 'Distinct' and 'Unique' but provides no definition.

MahyarTF and HoangHugo describe it perfectly.

I did a test to see the facts. Findings below the image, some interesting.

Distinct is the count of distinct values as in SQL:
Select count(distinct colname)
from tablename;

Unique is equivalent of:
with uniq(ResellerKey, cnt)
as
(
select ResellerKey, count(*) as cnt
from FactResellerSales
group by ResellerKey
having count(*) = 1
)
select count(*)
from uniq

Enough of the SQL.

This image demonstrates the results. From Excel, the blanks showed null. From CSV with consecutive commas it showed blank. Rather than two images I change the null to blank in the 4th column of the Excel load.

It is clear that null and blank are counted in Distinct. Multiple null/blank did not make it into Unique. Simple image for you to count yourself to full understand.

UPDATED IMAGE: I put a blank in No Duplicates to show that blank/null can be considered Distinct.

Interestingly, I had to close PBI Desktop to update the Empty to 7% in No Duplicates column. Refresh didn't do it. Neither did Apply and Close and then Transform. The profiling must be cached.

[MahyarTF]

Hi,

Distinct mean : count all the values as 1, even if there was more than one.

Unique mean : count only the value that are not repeated in the particular column

Appreciate your Kudos

[HoangHugo]

Hi,

I found it from Online

“Distinct” means total number of different values regardless how many times it appears in the dataset. A name appears in the list multiple times is counted as 1 distinct count.

Whereas, the “Unique” value is total number of values that only appear once.