Power Query’s Extract Text Feature

This will be a short post, as today we are leading our second sold out Power Query workshop at http://powerquery.training/course.  I wanted to make sure I still got something out for my readers today though.  This time I’m looking at a feature that was added in the August Power Query Update: Extract Text.

You can find these commands on both the Transform and the Add Column tabs, with the former just converting your selected column, and the latter creating a new column of results while preserving the original column.

SNAGHTML196640f7

Here’s what’s interesting to me about these functions:

They replicate the LEN(), LEFT() and RIGHT() functions, saving you having to build them manually as I discuss here.  This is handy, and pretty seamless.

Here, you’ve got a table of words, and the second column is generated using Excel’s LEFT() function.  The final column was generated by:

  • Selecting the Word column –> Add Column –> Extract –> First Characters –> 4

image

Nice and consistent with Excel’s LEFT() function.

Likewise, Last Characters replicates the RIGHT() function by going to:

  • Selecting the Word column –> Add Column –> Extract –> Last Characters –> 4

image

And Length replicates the LEN() function:

  • Selecting the Word column –> Add Column –> Extract –> Length

image

The Range function is a user interface implementation of what should be equivalent to the MID() function.  In this case, however, it’s still has the following issues for Excel pros:

  • It is Base 0, meaning that you want to start at the 3rd character of the text string, you need to specify that you want to start at character 2 (Power Query starts counting at 0, not 1)
  • If you provide a value for the “number of characters to return” that is larger than the total number of characters – the starting character, you’ll get an error.  (Unlike the MID function)

So when you try to use Range in place of MID as follows:

  • Selecting the Word column –> Add Column –> Extract –> Range
  • Starting Number: 5
  • Number of Characters: 4

You get this:

image

Ugh.  And correcting to subtract one from the starting index, you get this:

image

Better, but still errors.

Honestly, I was hoping the user interface implementation would solve those issues building the more complicated code shown in my blog post on the subject.

So, at the end of the day, it’s awesome, but still doesn’t offer full “Excel parity”.  And if you want that, you’ll need to learn to work with formulas in Power Query.

The good news?  We teach how to do that in our Power Query workshop.  In addition, we’ve just announced a new registration intake.  If you’re interested in learning how to master Power Query, check it out at http://powerquery.training/course.

5 thoughts on “Power Query’s Extract Text Feature

  1. Has the location or name of this changed in subsequent updates? I can't find it. I see something called 'Length" and all it does is the equivalent of the LEN function. It replaces my text with the number of characters.

  2. Hi Ken,

    Thank you for all the wonderfull insights into Power Query!

    I do a lot of text extracting with power query and find it easier and more transparant to use the split column function.

    In your example I would add a double 'world'column, split it by position (9 => 5+4), and split it again by position 5. The remainder is a column with the 4 positions you want and without errors!

    Cheers and keep up the good work!
    Mickey

  3. Hi Shamesh,

    Sorry for the delay in replying but if you are still looking for some help with this, please post in our free help forums. We do have one specifically for Power Query questions: https://www.excelguru.ca/forums/forumdisplay.php?19-Power-Query-(Get-amp-Transform)-amp-M-code. Please note that you must first set up a user account to post on the forum. Information on how to use the forum is available here: https://www.excelguru.ca/content.php?299-Forum-Guide.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.