Power Query: Keep all Capital Letters with Text.Select in M-Code

/in /by

Rahim Ali asked me about how to keep only capitol letters. For example:

France: F
United States: USA
Republic of China: ROC
United Arab Emirates: UAE
South Korea: SK

(In this video I use names as examples to work with.)

This can be done with Excel’s Flash Fill but that’s not a dynamic solution. For something dynamic we use Power Query and it’s a little tricky and we make clever use of Add Column from Example.

We have to go into the m-code and convert:
Text.Remove to Text.Select. And then we can have Power Query select only A thru Z

And then … a TWIST!

We’ve got a problem with the character: É

I show you one way of dealing with that!

This video also includes a summary of Excel Days 2018 in Sofia, Bulgaria where I had the pleasure to give the keynote presentation and teach a 7-hr Master Class on Excel and Power Query.

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2