Chapter 39: Transforming Data with Power Query
39
FIGURE 39.13
The Extract command allows you to pull out parts of the text found in a column.Last Characters Transforms a given column to show a specified number of characters from
the end of text in each row (similar to Excel’s RIGHT function).
Range Transforms a given column to show a specified number of characters starting from
a specified character position (similar to Excel’s MID function).
Text Before Delimiter Transforms a given column to show only the text that comes
before a specified delimiter.
Text After Delimiter Transforms a given column to show only the text that comes after a
specified delimiter.
Text Between Delimiters Transforms a given column to show only the text between two
specified delimiters.Applying the Extract command to a column will effectively replace the original text with the results of the operation
you choose to apply. That is, the original text will not be visible in the table after applying the Extract command. For
this reason, it’s often useful to first copy the column and perform the extraction on the duplicate column.
You can create a copy of a column by right-clicking the column and selecting Duplicate Column. When the duplicate
column is created, it will be the last column (at the far right) of the table.
Extracting first and last characters
To extract the first N characters of text, highlight the column, select Extract ➪ First
Characters, and then use the dialog box shown in Figure 39.14 to specify the number of
characters that you want to extract. In this case, the first three characters of the Phone
field will be extracted.