Remove Duplicates not working?

[alicia_pbi]

I have run into this issue many times and wanted to see if anyone has any feedback.  In this specific case I have 2 lists of user names.  I append the user names to a new query to have a singular list of user names.  I think TRIM the text to remove any spacing.  I then select remove duplicates in the Query Editor.  I close and apply my changes.  When I try to create a relationship between my list of user names and other tables I get a many to many notification.  I then pull all user names and do a count and find a single user name is listed twice even after removing duplicates and trimming the text.  Has anyone else had this and am I missing an obvious reason for my issue i.e. user error?

I am not able to provide the query because it has work data however below is the query that I am using that is still resulting in duplicates.

let
    Source = Table.Combine({#"username", #"user name"}),
    #"Filtered Rows" = Table.SelectRows(Source, each [User Name] <> null and [User Name] <> ""),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"User Name", Order.Descending}}),
    #"Trimmed Text" = Table.TransformColumns(#"Sorted Rows",{{"User Name", Text.Trim, type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Trimmed Text")
in
    #"Removed Duplicates"

Thanks in advance for helping me solve this mystery.

[az38 · Accepted solution]

Hi @alicia_pbi 

remove duplicates is a case-sensitive function. try UPPERCASE or LOWERCASE before it

do not hesitate to give a kudo to useful posts and mark solutions as solution

[az38 · Accepted solution]

Hi @alicia_pbi 

remove duplicates is a case-sensitive function. try UPPERCASE or LOWERCASE before it

do not hesitate to give a kudo to useful posts and mark solutions as solution

[alicia_pbi]

@az38  thanks for your information!  out of curiousity is it the same logic when using the Merge Queries?  For instance if I have user names "username" & "Username"  will they merge or will they not merge as they are considered unique?

Thanks!

[az38]

@alicia_pbi 

for DAX relationships it doesnt matter,

for power query case seems important, see this simple test

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[irobba · Accepted solution]

Just to elaborate on this as its an issue I have run in to before as well, as previously said PowerQuery is value case sensitive whilst DAX is not. So when you use Remove Duplicates in PowerQuery, it will not consider 2 values in different cases to be duplicates, but then when you create the relationship in DAX, it will consider them to be duplicates - hence the many to many. 

If you have to use the text field as a key for a relationship, trim as you have already, but then also force the case before de-duping so you'll get a result more like you are expecting and give you the relationship you want in DAX.  And as per az38's post, do the same thing prior to doing your merges in PowerQuery as well.