Table value differs in Power Query and Microsoft SQL table

[hanskerom]

Hi,

Data source:

SQLEXPRESS

I have a database in Microsoft SQL Express I want to extract values from; however, Power BI edits the data and creates duplications that breaks my data-model.

Relevant primary key values in Microsoft SQL database:

jUMAAALfrtVzQA

juMAAALfrtVzQA

Power Query with SQL Server connection changes this to:

jUMAAALfrtVzQA

jUMAAALfrtVzQA

For some reason, the "u" in the database table becomes "U" in Power Query hence editing the input from the database table.

Anyone know how to fix this?

[AlexisOlson · Accepted solution]

DAX is not case-sensitive (Power Query is though) and it's picking one representative between the two "duplicate" values.

See this prior thread for more detail:
https://community.powerbi.com/t5/Desktop/Single-character-in-Text-field-changing-from-upper-to-lower...

[hanskerom]

Thank you for the quick reply @AlexisOlson ! The cases look related, but I am struggeling to see how I can proceed if this is default.

What is the reason for Power BI to do this? And why can I not choose if I want this edit? I have a large dataset and if this is default I cannot trust the data when the primary key is changed - even if there are different values elsewhere in the table.

This is an extract from SQL on the two values:

As you can see, these are clearly not duplicates.

As I want to relate the "puid" to other tables with relevant information, I need to preserve these values.

@AlexisOlson - I saw you proposed workaround, but I don't understand how to test it. Could you please advise?

[AlexisOlson · Accepted solution]

In this situation, I would probably create an index to use as a key instead of [puid] across all the relevant tables or transform the key in all tables to ensure uniqueness.

On possible transformation for the latter might be to tag each character as lowercase or uppercase with a symbol. For example, "juMA" --> "-j-u+M+A" and "jUMA" --> "-j+U+M+A". These will stay distinct regardless of case sensitivity.

[hanskerom]

[AlexisOlson · Accepted solution]

DAX is not case-sensitive (Power Query is though) and it's picking one representative between the two "duplicate" values.

See this prior thread for more detail:
https://community.powerbi.com/t5/Desktop/Single-character-in-Text-field-changing-from-upper-to-lower...