BI Desktop Not Recognizing Difference Between Upper and Lower Case Letters

[rbowen]

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

[eliasayyy]

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]

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?

[eliasayyy · Accepted solution]

oh my bad here use this https://invisibletext.com/
insetead of the u+200e just copy this text in this link

[rbowen]

That did the trick!  Thank you.

[Idrissshatila]

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]

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?

[Greg_Deckler]

@rbowen In short, yes. I demonstrate the technique in this video:

[rbowen]

Yikes!  While I completely the 'case insensitivity' thing is ridiculous (much like the 'additivity' issue), the solution in your video is well beyond my BI/PowerQuery kung fu - especially given the sheer number of alphanumeric combinations in my data. Was really hoping there would be a relatively straightforward way to do this - surely there's a way in PowerQuery to take a column and either convert it's existing values to unicode, or create a new column based on the original and parse those values into unicode. It's becoming increasingly clear I may be indulging in wishful thinking. Perhaps I need to investigate doing the conversion on the front end with SQL.