Forum Discussion

Ravindra_'s avatar
Ravindra_
Frequent Visitor
1 year ago
Solved

Issue with Case Conversion in Power BI Table View

Dear Microsoft Support Team, I hope you are doing well. We have encountered an issue in Power BI where the case of certain strings is being altered when switching to Table View. This behavior is une...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi Ravindra_ 

    Power BI automatically normalizes text case when loading data, treating "English" and "enGLISH" as the same value and picks whatever gets loaded first. You convert the text to binary and then the binary format to text and append it to the original. Extracting the actual text in DAX will, however, return just the first version that gets stored.

    Here's s sample custom column:

    let 
toBinary = Text.ToBinary([TextColumn]),
toText = Binary.ToText(toBinary)
in [TextColumn] & "_" & toText

     

     

OwenAuger's avatar
OwenAuger
Super User
1 year ago

Hi Ravindra_ 

This is actually by design for Power BI.


I recommend reading this article which describes this behaviour in detail:

https://www.sqlbi.com/articles/letter-case-sensitivity-in-dax-power-bi-and-analysis-services/

To quote the article:

"...when values are inserted into a table, two strings that differ only because of the casing are stored with the same index – thus resulting in the same string."

 

Generally speaking, the first case encountered when values are loaded to a table is used for all occurrences in that column.


As a consequence, Power BI models are not able to distinguish between values in a particular column that differ only by case. This is a feature of the Power BI version of the Tabular engine that we have to work around, for better or worse.

 

Regards