Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
alicia_pbi
Microsoft Employee
Microsoft Employee

Remove Duplicates not working?

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.

2 ACCEPTED SOLUTIONS
az38
Community Champion
Community Champion

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


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

View solution in original post

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. 

View solution in original post

4 REPLIES 4
az38
Community Champion
Community Champion

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
alicia_pbi
Microsoft Employee
Microsoft Employee

@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
Community Champion
Community Champion

@alicia_pbi 

for DAX relationships it doesnt matter,

for power query case seems important, see this simple test

Безымянный.png

 

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

LinkedIn


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

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. 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.