Forum Discussion

rbowen's avatar
rbowen
Helper III
3 years ago
Solved

BI Desktop Not Recognizing Difference Between Upper and Lower Case Letters

Greetings All - 

 

I'm working with a data table containing some account information. Part of that information is account codes that contain a main account number, and a sub account number. The sub account determines the location and store name of where a sale was made. The sub account number contains of mix of letters and numbers, for example H00 or h00. For this example, the upper case H and lower case h refer to two different sites and store names - yes, it's a terrible numbering scheme but it's what I have to work with. PowerQuery can tell the difference between these, but BI Desktop can't and it's messing up my sales data calculations. Desktop thinks the the two different sub accounts are the same and summing them together instead of separately. There's probably a way to remedy this with unicode in PowerQuery but I'm unclear as to how that would be done.  Any suggestions on how to do this?

 

Thank you

 

 

 

Need Help

10 Replies

  • Idrissshatila's avatar
    Idrissshatila
    Super User
    3 years ago

    Hello rbowen ,

     

    so you click on the column you want to have them all upper case and then under transform the is a format option = > you click on it and then click on upper case and it will make it happen.

     

    If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

    Follow me on Linkedin
    Vote For my Idea 💡

     

     

    • rbowen's avatar
      rbowen
      Helper III
      3 years ago

      Unfortunately, I can't do that. I have to maintain the upper/lower case mix because they reference different locations based on the letter case. There are multiple combinations of this with different letters as well - i.e., A/a, B/b, I/i, etc. Is there a way in PowerQuery to create a custom column of the sub account column using Unicode?

  • eliasayyy's avatar
    eliasayyy
    Memorable Member
    3 years ago

    hello rbowen 

    lets say you have this table 

     


    as you see it is adding them up together the lowercase and uppercase, 

    a wrokaround is to add an invisible space to the text with lower cases do this


    add a new custom column 

    if 
 Text.StartsWith([Data], Text.Lower(Text.Start([Data], 1))) then  [Data] & "<u+200e> "  else [Data]

     

     

     



    • rbowen's avatar
      rbowen
      Helper III
      3 years ago

      Thanks Anon, this appears to be getting closer, but I'm not getting the clean lower case values showing in your example. Here's the M code I'm using:

       

      =if
      Text.StartsWith([SubAccountNumber], Text.Lower(Text.Start([SubAccountNumber], 1))) then [SubAccountNumber] & "<u+200e> " else [SubAccountNumber]

       

      And what I end up with is this:

       

       

      The SubAccountNumber reference column is formatted as text, as is the new column. The reference column also contains straight numeric values (100, 200, 300, etc) and I'm wondering if that's throwing off the calculations for the new column - although, as I said, the reference column is formatted as text.  Am I missing something?