How do I make an M query editor if statement about whether the first three letters are all caps?

[alicek]

Hello, 

In one column ("department") I have the same department sometimes written out and sometimes starting with an all caps three-letter acronym and then written out. 

I first created a column that extracted all values from "department" after the first delimiter (space). Therefore, I have the original mixed column, and a new column that is either the department written out (with the acronym removed) or the department missing its first word.

I have figured out how to do an M if statement that I could use to clean this up, but it still requires the manual spelling out of 52 different departmen acronyms. The code is this (but 52 "or"s): if Text.Contains([department], "ABC") or Text.Contains([department], "CDE") or Text.Contains[department], "EFG") ... then "[Text.After.Delimiter] else [department]"

Does the community have an idea on how I could instead re-write the if statement logic to search for any value in [department] whose first three letters are all-caps? It would just be an easier, cleaner solution. 

department	Text After Delimiter	Final Desired Result!
APD Adult Probation	Adult Probation	Adult Probation
DPH Public Health	Public Health	Public Health
Adult Probation	Probation	Adult Probation
Public Health	Health	Public Health

I tried "column from example" by the way, but it was a little too tricky for it to understand, and wouldn't show me enough of my rows to give it a full example. It start just specifying the number of characters to take from the right, but that number will be different for every department.

I've also tried to somehow incorporate Text.Upper but I can't quite figure out of that works, or if it is only a command to make something uppercase.

[Anonymous · Accepted solution]

Hi @alicek ,

Thanks for the reply from @kpost .

Go to Power Query and create a custom column:

if Text.Start([department], 3) = Text.Upper(Text.Start([department], 3)) then Text.Middle([department], Text.PositionOf([department], " ") + 1, Text.Length([department])) else [department]

The page result is shown below:

If you have any other questions please feel free to contact me.

The pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[alicek]

This was perfect, thank you so much @Anonymous !!!

[kpost]

I'd use the fact that the equivalent 21 bit Unicode values generated by Character.ToNumber(), for capital English letters, are 65 through 90 when represented as an integer.

A = 65
B = 66
C = 67
.....
Z = 90

So you could do this to get the value of the first character, for example:

= Character.ToNumber(Text.At([department], 0))

this to get the 2nd:

=Character.ToNumber(Text.At([department], 1))

And this to get the 3rd:
=Character.ToNumber(Text.At([department], 2))


and verify all of these are greater than or equal to 65 and less than or equal to 90


if... and... and... and... etc.

Good luck 👍
///Mediocre Power BI Advice, but it's free///