Power Bi treat values with different case as duplicates in refersh or relationships

[rumittal]

Hi All,

I have a table of unique values - lets call them Configurations, and seems its treating two values like 'clientConfig' and 'ClientConfig' with same spelling but different case as Duplicates.

When I apply 'Remove Duplicates' in query editor, it doesnt remove one of them but still treating them as duplicates.

Could you please help, if there is a way for power bi to treat these as 2 separate value in below both cases?

This is causing two issues :

1) In some cases, I am not able to create 1:many relationship with Configuration table on 1 side of it

2) And not able to refresh my table also , it fails with error : ''contains a duplicate value and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table."

[Icey · Accepted solution]

Hi @rumittal,

Power BI Desktop's query editor and M language is case-sensitive while Power BI Desktop's data model and DAX language is not case-sensitive. Someone has submitted this problem here, you could click to vote it up.

Workaround is to create a surrogate key like described here:

Create a Dimension Table with Power Query: Avoid the case sensitivity bug!

Case Sensitive relationships

Power BI And Case Sensitivity,

Creating Case Sensitive Table Relationships.

Hope these documents can help you.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.