Text modifications with Power Query (Get and Transform)

This tutorial describes modifications, that can be done with Text in Power Query (from Excel 2016 called Get and Transform). All mentioned buttons can be found in Trasform / Text Column.

Split column

Splits column to multiple columns, using delimiter or fixed number of characters. Something like “Text to columns” in spreadsheet.

When working with delimiter, in next step has to be declared which character is used as delimiter, and whether all delimiters should be used – or only the left one or the right one.

Similarly splitting based on number of characters can be used.

Merge columns

Merge columns means, well, just merging of columns… Before merging we have to select the columns used for merging, if necessary which character should be used for their separation and the name of merged column.

Format

It is not real formatting – it is more or less changing of data.To be more specific:

lowercase

• Makes lowercases from everything
• Makes “abc” from “Abc”

UPPERCASE

• Makes uppercase from everything
• Makes “ABC” from “Abc”

Capitalize Each Word

• Well, this capitalizes each word…
• Makes “Abc Def” from “abc def”)

Trim

• Removes spaces at the beginning, removes spaces at the end and makes one space from multiple spaces anywhere else.
• Makes “abc def” from ”    abc    def   “Z

Clean

Removes non printable characters

Add Prefix

Add something before text

Add Suffix

Add something to the end of text

Extract

Equivalent of some text functions from spreadsheat.

Length

• Provides the number of characters
• Provides “3” for “abc”

First characters

• Provides some characters from the beginning.
• You can get first two characters from “abc” and see the result “ab”

Last characters

• The same, just from the end…

Range

• Provides some number of characters from some characters.
• For example from “abcdef” provides two characters from the third characters – resulting in “cd”