Power QueryExercises

Trim, clean and capitalise text

Standardise text so equal things compare equal.

~9 min read · beginner · Module 6: Text transformations

By Tahir Nas · Examples run in our simulator · Published · Updated

No prerequisites: you can start here.

" North", "north" and "NORTH " are three different values to Power Query. Filters, merges, Group By and Remove Duplicates treat them separately until you standardise them.

What each function does

  • Text.Trim removes spaces at the start and end. It does not collapse double spaces in the middle.
  • Text.Clean removes non-printing control characters, such as line feeds pasted from other systems.
  • Text.Upper / Text.Lower change the case of every letter.
  • Text.Proper capitalises the first letter of each word and lowercases the rest, so "mcdonald" becomes "Mcdonald". Check names with internal capitals.
let
    Source = #table({"Raw"}, {{"  north "}, {"SOUTH"}, {"west#(lf)"}, {"new  york"}}),
    Cleaned = Table.AddColumn(Source, "Clean", each Text.Proper(Text.Trim(Text.Clean([Raw]))), type text),
    Lengths = Table.AddColumn(Cleaned, "Length", each Text.Length([Clean]), Int64.Type)
in
    Table.RemoveColumns(Lengths, {"Raw"})

Result

CleanLength
North5
South5
West4
New York9

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

#(lf) is M's escape for a line feed; Text.Clean removes it. The double space in "New York" survives Trim.

Several columns at once

Select several columns before choosing Transform → Format, and you get one Table.TransformColumns step with a pair for each column, such as {{"FirstName", Text.Trim, type text}, {"LastName", Text.Trim, type text}}.

Collapsing inner spaces

There's no built-in function that collapses repeated inner spaces. Split on spaces, drop the empty pieces and join again:

Text.Combine(List.Select(Text.Split("new   york  city", " "), each _ <> ""), " ")

Result

new york city

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Clean before you compare or combine

Trim and fix case before removing duplicates, merging or grouping. Otherwise near-identical values stay apart. When combining names, clean each part first so you don't produce double spaces.

Where to find this in Excel

Excel for Windows
Transform > Format > Trim, Clean, Capitalize Each Word, UPPERCASE or lowercase
What you see
The command changes the selected column in place. Add Column > Format runs the same command into a new column.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Add a column based on a data type (Power Query) (Microsoft Support).

Common mistakes

  • Expecting Text.Trim to collapse double spaces inside a value.
  • Using Text.Proper on codes or surnames with internal capitals.
  • Cleaning text after merging or grouping, when the mismatches have already split the results.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop: Transform → Format offers lowercase, UPPERCASE, Capitalize Each Word, Trim, Clean, Add Prefix and Add Suffix. Add Column → Format creates a cleaned copy instead.
  • Excel's TRIM worksheet function does collapse inner spaces; Power Query's Text.Trim doesn't. People switching from formulas often expect the Excel behaviour.
  • The simulator's Text.Trim removes whitespace at both ends; Text.Clean removes control characters such as line feed and tab.
Functions in this lesson:Text.TrimText.CleanText.ProperText.UpperText.Lower

Next step

Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.

Practise: Clean names and build a full name (logistics)

Practice this

Each exercise runs in the browser and is checked against fresh data, not just the example.