Forum Discussion
Remove Duplicates not working?
Hi Anonymous
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.
Anonymous
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.