Power Query’s Extract Text Feature

This will be a short post, as today we are leading our second sold out Power Query workshop [Note: the live workshops have been replaced by Power Query Academy].  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.

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

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

And Length replicates the LEN() function:

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

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:

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

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 our Power Query Academy.