Changing the ID letter case after loading into model

[Anonymous]

Hi,
I encountered a strange case, probably bug, with an ID column. The ID's in my database are unique for each record, but only including case-sensitivity (which I know that Power Query applies).
So, in Power Query I have for example 3 records with ID's, which are almost identical, but still different including case sensitivity, as below (the last 3 signs are different):
a031o00001EpIYH
a031o00001EpiYh
a031o00001Epiyh

As far as I'm in Power Query, everything is OK, there are no duplicates in the database. But after loading into model, something really strange happens - all three ID's convert into one identical - a031o00001EpIYH and I can see it on Data label after filtering.
First I thought, that except Power Query, DAX and relations could be not case-sensitive (which is problematic enough and I think it really should be consistent), but why it changes and unifies my IDs ?

For that reason, I cannot create any relation, because Power BI claims, that there are duplicates in this column. When I open Power Query again, the ID's differs correctly, so I can't de-duplicate anything.
The ID column type is "Text". The main question is - why Power BI during loading into model can change capitals into lowercases and contrariwise in the Text columns? Does it also can make such changes in other columns, like with "Last name" ?
And is there any clarification for such strange case or is it a bug or something?
I will be grateful for any help 🙂

[v-lionel-msft]

Hi @Anonymous ,

I created a test table as follows：

Although the software is case sensitive in the power query, but in data view, the software isn't case sensitive when the value is exactly the same except for the case. In other words, DAX is not case sensitive.

For your Problem, it has been mentioned many times in the forum, maybe you can refer other answers:

Creating Case Sensitive Table Relationships  

Best regards,
Lionel Chen

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

[MartynRamsden]

Hi @Anonymous 

Chris Webb wrote a great blog post about Power BI case sensitivity in October 2019.

You can find it here: blog.crossjoin.co.uk/2019/10/06/power-bi-and-case-sensitivity/ 

There's a section near the end where he explains how to add a zero-width spaces in Power Query to force case sensitivity in the Power BI engine.

Hopefully this will help in your situation.

Best regards,

Martyn

If I answered your question, please help others by accepting it as a solution.