Part V: Understanding Power Pivot and Power Query
By Number of Characters Allows you to split a column based on a specified number of
characters. This is useful for parsing uniform text at a defined character position.
In the example shown in Figure 39.16, the contact names are made up of last names, first
names, and middle initials, all separated (delimited) by commas. So, the By Delimiter
option is the one we’ll use.
You can highlight the ContactName field, right-click, and then select Split Column ➪
By Delimiter. This will activate the Split Column by Delimiter dialog box illustrated in
F i g u r e 39.17.
FIGURE 39.17
Splitting the ContactName column at every occurrence of a comma
The inputs here are as follows:
Select or enter delimiter Use the drop-down to choose the delimiter that will define
where the values should be split. If your delimiter is not listed as a choice in the drop-
down, you can select the Custom option and define your own.
Split Select how you want Power Query to use the specified delimiter. Power Query can
split the column only on the first occurrence of the delimiter (the leftmost delimiter),
effectively creating two columns. Alternatively, you can tell Power Query to split the col-
umn only on the last occurrence of the delimiter (the rightmost delimiter), again creating
two columns. The third option is to tell Power Query to split the column at each occurrence
of the delimiter.
Advanced options By default, selecting the option to split the column at each occur-
rence of the delimiter will create as many columns as there are delimiters. You can use the
Advanced options to override the default and limit the number of columns to create.
