Text.Trim
Text function · By Tahir Nas · Examples run in our simulator · Published · Updated
Removes leading and trailing whitespace.
When to use it
Use it on every text key and category column straight after import, before filters, merges or duplicate checks. Leading and trailing spaces are the most common reason values that look equal don't match.
Syntax
Text.Trim(text as nullable text, optional trim as any) as nullable textParameters
- trim
- Optional characters to trim instead of whitespace.
Returns
The trimmed text.
Example
Text.Trim(" North ")Result
North
Example: trim other characters
The optional argument lists characters to remove from both ends.
Text.Trim("--A-17--", "-")Result
A-17
Both results are produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M, and checked on every build.
Good to know
- Inner double spaces stay.
Common mistakes
- Expecting it to remove double spaces inside the text.
- Trimming after the merge that needed clean keys.
The matching command in Excel
- Excel for Windows
- Transform > Format > Trim (Add Column > Format > Trim keeps the original)
- What you see
- The command runs on every value of the selected text 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).
Exercises that use Text.Trim
- beginner~8 minFree
Reorder steps so cleaning happens before grouping (kite shop)
The query groups regions before cleaning them, so " north" and "North" are counted separately. Fix the step order.
- intermediate~7 minFree
Fix a step that silently skips the cleaning step (kite shop)
The query runs, but its totals are split across " north" and "North": the Group step reads Source instead of the cleaned step.
- beginner~7 minFree
Convert Yes/No answers to true, false or null (charity)
The marketing opt-in column mixes Yes, Y, no, N, blanks and nulls. Turn it into a proper true/false column.
- intermediate~9 minPro
Clean every column name, including future ones (survey)
Trim and capitalise all column headers with one dynamic step instead of renaming each column by hand.
- advanced~10 minPro
Classify quantities as OK, Missing, Invalid or Negative (warehouse)
Give every stock count a validation status so the warehouse knows which rows to recount.
- intermediate~7 minPro
Count each email once, whatever its case or spacing (petition)
The same address appears as Amara@Mail.example and ' amara@mail.example'. Build a clean list of unique emails.
Learn more
- Trim, clean and capitalise text lesson
- Power Query data cleaning recipes for messy exports guide
- Power Query text functions cheat sheet (tested) guide
Related functions
Official documentation: Text.Trim on Microsoft Learn.