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.Trimremoves spaces at the start and end. It does not collapse double spaces in the middle.Text.Cleanremoves non-printing control characters, such as line feeds pasted from other systems.Text.Upper/Text.Lowerchange the case of every letter.Text.Propercapitalises 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
| Clean | Length |
|---|---|
| North | 5 |
| South | 5 |
| West | 4 |
| New York | 9 |
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.
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.
- beginner~8 minPro
Clean names and build a full name (logistics)
First and last names have stray spaces and random capitals. Clean them and combine into one Full Name column.
- beginner~8 minPro
Clean names and build a full name (software)
First and last names have stray spaces and random capitals. Clean them and combine into one Full Name column.
- beginner~8 minPro
Clean names and build a full name (hospital)
First and last names have stray spaces and random capitals. Clean them and combine into one Full Name column.
- intermediate~7 minPro
Flag tickets that mention billing, in any case (telecom)
Add an Is Billing flag when Topic contains "bill" regardless of capitalisation.
- intermediate~7 minPro
Flag tickets that mention billing, in any case (saas)
Add an Is Billing flag when Topic contains "bill" regardless of capitalisation.
- beginner~6 minPro
Keep only the digits of phone numbers (dental)
Phone numbers were typed with spaces, brackets, dots, dashes and plus signs. Keep just the digits.
- beginner~6 minPro
Keep only the digits of phone numbers (tutoring)
Phone numbers were typed with spaces, brackets, dots, dashes and plus signs. Keep just the digits.
- intermediate~7 minPro
Build a one-line address that skips missing parts (subscribers)
Combine Line1, Line2, City and Postcode with ", " between them, leaving out Line2 when it's null.
- intermediate~7 minPro
Build a one-line address that skips missing parts (suppliers)
Combine Line1, Line2, City and Postcode with ", " between them, leaving out Line2 when it's null.