Extract only letters from a mixed string in Power Query and Power BI

This is a quick method about extracting only letters from a string. It is part of the Week2 “Preppin’ data” challenge.

Task for extracting letters from a string

Image you have a string like so: “10.ROADBIKES.423/01” and would like to extract only “ROADBIKES”.
Power Query actually has a function for this purpose: Text.Select. It takes 2 parameters:

  1. The text to select from
  2. A list of characters that shall be selected

For the given example the code would look like so:

Text.Select( "10.ROADBIKES.423/01", {"A".."Z"})

This function is always case sensitive as there is no optional parameter that accepts a comparer function.

Easy application

Although the function is not available through the UI, it can nonetheless easily be applicated. Just use a different dummy-text transformation function and then edit the code afterwards. That way you only have to type in a tiny fraction of the code:

Tweak to Text.Select for extracting letters from a string

 

Please check the video if you want to see how to use this function without having to manually code it in the advanced editor. You’ll also learn how to apply aggregations on groupings easily:

 

Enjoy and stay queryious 😉

Comments (4) Write a comment

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz