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 (7) Write a comment

  1. The original data type of Order Date and Shipping Date in the source file “PD 2021 Wk 2 Input – Bike Model Sales.csv” are text. I tried to change them to date but found a lot of errors like:

    DataFormat.Error: We couldn’t parse the input provided as a Date value.
    Details:
    15/05/2020

    What’s wrong and how can I fix it? Please advise.

    Reply

  2. How would you keep only vowels, ie. A, E, I, O and U. I have tried various Text.Select and cannot find a solution.

    Reply

    • Please try Text.Select( “10.ROADBIKES.423/01”, {“A”, “E”, “I”, “O”, “U”})

      Reply

  3. @Imke,
    That is the exact expression I have attempted and PQ errors on that statement. It shows the error as ocuring after the comma after the letter A.

    Reply

Leave a Reply