DistinctCount function counting values greater than 1 despite removing all duplicates in Power Query

[michael_ma]

Hi everyone, 

In power query I transformed a column by removing all the duplicates so that each value in the column will be distinct from one another. 

However, when I do a count of the column in a report, I'm receivng values from the column that have a count greater than 1.

The total is the correct value that I'm looking for, but confused on why there are counts greater than 1. When I query a distinct count in our source system I get the same total as the visual above, but when I query a distinct count in power bi I get a lower total value than what I expect. 

Does anyone know why there are counts greater than 1 despite removing all duplicates in power query? Is there a dax expression that can do a distinct count and reflect the same total as shown above? 

[OwenAuger · Accepted solution]

Hi @michael_ma 

As a hunch, I would suggest you look into whether this is a case-sensitivity issue.

Power Query is case sensitive when comparing strings so, when removing duplicates, values that differ only in case will not be treated as equal.

However, after loading tables to the data model, strings are case insensitive, and only one representative instance of each string will be loaded in a given column. Trailing spaces are also removed from all string alues.

I have attached a small example:

3 distinct values in Name column in Power Query:

One distinct value after loading:

A couple of articles to refer to:

https://www.sqlbi.com/articles/letter-case-sensitivity-in-dax-power-bi-and-analysis-services

https://blog.crossjoin.co.uk/2019/10/06/power-bi-and-case-sensitivity/

Regards