Part V: Understanding Power Pivot and Power Query
FIGURE 39.14
Extracting the first three characters of the Phone field
To extract the last N characters of text, highlight the column, select Extract ➪ Last
Characters, and then use the dialog box to specify the number of characters you want to
extract.
Extracting middle characters
To extract the middle N characters of text, highlight the column, and select Extract ➪
Range. The dialog box shown in Figure 39.15 will activate.
The idea here is to tell Power Query to extract a specific number of characters starting from
a certain position in the text. For example, the SicCode field is a four-digit field. If you
wanted to extract the two middle numbers of the SicCode, you would tell Power Query to
start at the second character and extract two characters from there.
As you can see in Figure 39.15, Starting Index is set to 2 (starting at the second character),
and Number of Characters is set to 2 (extract two characters from the starting index).
Splitting columns using character markers
Have you ever gotten a data set where two or more distinct pieces of data were jammed
into one field and separated by commas? For example, a field called Address may have a text
value that represents “Address, City, State, ZIP.” In a proper data set, this text would be
split into four fields.
In Figure 39.16, you can see that the values in the ContactName field are strings that rep-
resent “Last name, First name, Middle initial.” Imagine that you need to split this column
string into three separate fields.
