Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to 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
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.
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 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!
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
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
127 | |
109 | |
93 | |
70 | |
67 |