mancs's avatar
mancs
Regular Visitor
8 years ago
Status:
Done

Data Modeling Uppercase and Lowercase issues

Hello,

 

I'm pulling data from a MYSQL server and among the data that I have there several entries which have similar input however they differ from each other by having some of the letters in their composition UPPERCASE or LOWERCASE, the reasoning behind this is beyond me since I didn't create the database. Regardless, I need to accept all of the entries, however, whenever I apply my modifications to the query editor it transforms part of the entries from LOWERCASE to UPPERCASE values which create duplicates, which in turn doesn't allow me to have a table with unique values. There several examples of this disturbance, the screens bellow show once such case. Any ideea on how to retain the values unchanged from Query Editor to Data View?Query Editor

 

Data View

 

Data Modeling

39 Comments

  • v-haibl-msft's avatar
    v-haibl-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    mancs

     

    This is by design. Power BI workbook uses a case insensitive language setting. As a result, strings in the same column which differ only by cases will be consolidated into a single string depending on whichever happens to be imported first. Query Editor UI displays the strings in the original case before the Import, Data View shows the strings after the Import.

     

    Best Regards,
    Herbert

  • chr9219's avatar
    chr9219
    Frequent Visitor
    8 years ago

    Same is happening for me.  The data was imported into files correct and also looks correct in the Query editor, but is changing when I add the field to a table view.  For example,  I have one employee with the First name field is "Jane" and the Last Name field "Smith" and another employee with First Name field is "Mary" and the Last Name field is "SMITH".  In the table view both employees are showing with last name SMITH.  Or the reverse is happening they will both show as Last name Smith.  How can I get them to appear exactly how they were imported?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Please Microsoft PBI team to fix this issue...

     

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    this is not solved! please comment

  • k3rz0rg's avatar
    k3rz0rg
    Icon for Advocate III rankAdvocate III
    7 years ago

    In case someone new trying to find the solution, here it is.

     

    Query Editor > Add custom column

     

     

    Then apply modifications to that column.

    Hope this will help.

     

    Thanks,

    K3.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I am having the same issue -- this is not fixed.

     

    There needs to be a way to maintain the integrity of the case within a text field when the case is significant to the application.

     

    Andrew

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hello,

     

    This issue still impacting the quality of data presented by Power BI tool. The workarround proposed bellow do not serves to everyone intents. 

    In our case we ha a Data Quality solution which needs to identify records which haves the content in lower case, so this "typing manipulation" inside the tool is inpacting the disired behaviour.

     

    Follows the example:

     

    Power Query record

    Record shown in a table in the page

    Power BI Version 

     

    Best regards,

     

    Anderson Acosta

Completed Ideas