This post covers a few common text transformations in Power Query. When you are working with a column, always tell the Power Query Editor which column you are working with by selecting it first. After executing a few transformations, you can send the table back to Excel. By default, Excel creates a new table in a new worksheet when you click on Close and Load.

Extract Text to New Column

Suppose we have a column with some text in it. Perhaps it is a description of a product. You want to create a new column with the color name in it. If the color name is between delimiters, such as brackets or dashes, Power Query makes it easy. Highlight the column, Add Column, from Text, Extract, Text Between Delimiters.

Suppose we have a FullName column that we want to extract into FName and LName and still retain the original FullName column. First, select the FullName column, Add Column and Duplicate Column. Next, select your duplicated column, Transform, Split Column, by Delimter. Typically you would be using the space as a delimiter, but specify the delimiter here.

Format Capitalization

Go to Transform or Add Column, depending on what you need. Go to Format, Capitalize Each Word. Under the Format menu there are other choices. There is lowercase, UPPERCASE, Capitalize Each Word, Trim, Clean, Add Prefix, and Add Suffix. Clean removes all the control characters from the text value. This can include end of lines, tabs etc. Clean removes all control characters. Trim removes all spaces from the beginning and end of the text.

Blanks and null

Depending on the data and situation, you may want to replace blanks with null. If you see the word null in a cell, it really means nothing. If you see nothing in a cell (blank) it really means something, such as a space or perhaps another invisible character.

Split Two Pieces of Data in a Single Column

Suppose you’ve got two kinds of data in a single column. Suppose both types are text separated with a forward slash character. For example, you could have Country and Province. Perhaps you have Department and Position. We could use Transform, Split Column. What are the options? By Delimiter, By Number of Characters, By Positions, By Lowercase to Uppercase, By Uppercase to Lowercase, By Digit to Non-Digit, and By Non-Digit to Digit.

Split Full Name to First and Last

Suppose you have a column that has names such as Fred Smith, Fred J Smith, or Fred John Smith. Select the column. We could go to Add Column. Choose Extract. Text Before Delimiter and type a space to denote the delimiter. Okay. Now to extract the last name when we have a middle name, we could start by selecting the column with the Full Name and go to Add Column. Now we choose Extract, Text After Delimiter and go to the Advanced options. Enter the spacer delimiter. In the drop-down choose from the end of the input.

Watch Out when Merging Columns

Suppose you have the names of people in three columns: first, middle and last name. This is great, but you would like to also have a column that merges those names. Sometimes we don’t have a middle name. In our data, we have null when there is no middle name. It might be better to use Add Column because merging columns from the Transform tab adds a space for columns that have null values, whereas merging columns from the Add Column tab, ignores columns with null values. That’s a subtle difference that really makes a difference.