Data cleaning with Power Query

In short: select a column, then on the Transform tab open Format and pick, for example, Trim (removes edge spaces) or Capitalize Each Word. Split Column breaks text apart at a delimiter, and Replace Values swaps abbreviations or typos.

Try it now

Someone typed these names by hand. Some have spaces at the start or end, and the capitalization is all over the place. The preview shows the spaces exactly as they are stored, so you can see what Trim removes.

Task The names were typed in by hand: spaces at the start and end, some lowercase, some uppercase. Clean the Name column so every name has no edge spaces and starts each word with a capital letter.

	A	B
1	Name	Department
2	anna schmidt	Sales
3	BERND MEIER	Purchasing
4	carla weber	Sales
5	dirk VOGEL	HR
6	eva klein	Purchasing
7	Frank Lang	Sales

What the Format commands do

Command	Effect	M function
lowercase	everything lower case: ANNA becomes anna	Text.Lower
UPPERCASE	everything upper case	Text.Upper
Capitalize Each Word	first letter of each word upper case, the rest lower case	Text.Proper
Trim	removes spaces at the start and end	Text.Trim
Clean	removes non-printable control characters, such as line breaks pasted from web forms	Text.Clean

One trap if you know Excel's TRIM function: Power Query's Trim only touches the edges. Microsoft documents Text.Trim as removing leading and trailing whitespace, so double spaces inside the text stay. To fix those, use Replace Values and replace two spaces with one.

The same commands appear on two tabs. On Transform they change the selected column in place. On Add Column they create a new column with the result and leave the original alone.

Split a column by delimiter

"Smith, Anna" in one cell is fine to read but useless for mail merges or sorting by first name. To split it:

1. Select the column.
2. On the Home tab choose Split Column, then By Delimiter.
3. Enter the delimiter, here a comma followed by a space, and choose whether to split at each occurrence or only the left-most one.

The new columns are called Employee.1 and Employee.2 at first. Double-click a header to rename it.

Replace values

Replace Values swaps text in every row of the selected column. By default it also replaces text that is only part of a cell. Tick Match entire cell contents to replace only cells that match exactly. The formula bar shows the difference: Replacer.ReplaceText for parts of a cell, Replacer.ReplaceValue for whole values.

One more exercise: split names, expand abbreviations

Task Split the Employee column into last name and first name (delimiter: comma and space) and replace the abbreviation "Acct." with "Accounting" in Department.

	A	B
1	Employee	Department
2	Schmidt, Anna	Acct.
3	Meier, Bernd	Sales
4	Weber, Carla	Acct.
5	Vogel, Dirk	HR
6	Klein, Eva	Acct.
7	Lang, Frank	Sales

This exercise is part of Premium.

Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.

See Premium Already Premium? Log in

The first exercise in this lesson is free.

The M code behind the steps

After the second exercise, Applied Steps lists "Split Column by Delimiter" and "Replaced Value". The formula bar shows:

= Table.SplitColumn(Source, "Employee", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Employee.1", "Employee.2"})
= Table.ReplaceValue(#"Split Column by Delimiter","Acct.","Accounting",Replacer.ReplaceText,{"Department"})

Each step takes the previous step's result as its first argument. That explains why deleting a step in the middle can break the next one: it now points to a column or step name that no longer exists. Also note that the new column names are fixed in the formula. If a later export suddenly has three parts instead of two, the third part does not get its own column automatically.

Common mistakes

Every first name starts with a space after splitting

The delimiter was only the comma. Either split on comma plus space, or add a Trim step for the second column.

Replace changes more than intended

Replacing "Acct." also changes "Acct. North". Usually that is what you want. If not, switch on Match entire cell contents.

Upper and lower case

Power Query treats "berlin" and "Berlin" as different values when replacing and filtering. Standardize the case before you filter or remove duplicates.

The commands in German Excel

English	German
Format: Trim, Clean	Format: Kürzen, Bereinigen
lowercase, UPPERCASE	Kleinbuchstaben, Großbuchstaben
Capitalize Each Word	Ersten Buchstaben im Wort großschreiben
Split Column: By Delimiter	Spalte teilen: Nach Trennzeichen
Replace Values	Werte ersetzen

Which Excel versions support this?

Every command on this page is part of the Power Query Editor in Excel 2016 and later for Windows and in Microsoft 365. On a Mac you need Microsoft 365. The Power Query overview has the details.

FAQ