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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Changing the ID letter case after loading into model

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 🙂

2 REPLIES 2
v-lionel-msft
Community Support
Community Support

Hi @Anonymous ,

I created a test table as follows

aa8.PNG

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.

aa9.PNG

 

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
Solution Sage
Solution Sage

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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