Forum Discussion
Difference between Distinct and Unique when using column distribution
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.