Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Ignore Case sensitive data in a table

Hi All,

 

I have created a third table in query editor to join 2 other tables. However I would like to ignore the case senstive data.

for eg: mdf and MDF, demand Generation & Demand Generation. How do i acheive this and make sure that whenever a user enters the data it records as one entry? 

Need Help
  • edhans's avatar
    edhans
    7 years ago

    The transformation to UPPER needs to be before you do the Table.Distinct command.

    Also, you may need to do a TRIM as well in the Transform menu. You could have "MDF" and "MDF " for example with trailing spaces.

    If you still see dupes, do a CLEAN in the Transform menu.

     

    Then do your Table.Distinct() command.

3 Replies

  • edhans's avatar
    edhans
    Community Champion
    7 years ago

    You can transform the data using Text.Lower() or use the menu on the Transform tab, Format, lowercase. (or upper if you like.) Then do your comparisons, merges, or show distinct values.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi,

       

      I converted the data to upper case. I still do see two values of the data that was incorrectly entered.

      and also once convereted to uppercase will i be able to join it to other tables(as they are in normal format).

      • edhans's avatar
        edhans
        Community Champion
        7 years ago

        The transformation to UPPER needs to be before you do the Table.Distinct command.

        Also, you may need to do a TRIM as well in the Transform menu. You could have "MDF" and "MDF " for example with trailing spaces.

        If you still see dupes, do a CLEAN in the Transform menu.

         

        Then do your Table.Distinct() command.