Text.Trim Function in Power Query M (5 Easy Examples)

If you have text with stray spaces or junk characters hanging off the start or end, Text.Trim is the function that cleans them off.

It returns the text with the leading and trailing characters removed. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Table of Contents

Syntax of Text.Trim Function

Text.Trim(text as nullable text, optional trim as any) as nullable text

where

text (required, nullable text). The text value you want to clean up.
trim (optional, any). The character or characters to strip from each end. It can be a single character or a list of single characters. Omit it to remove whitespace, which is the default.

Returns: a text value with the leading and trailing characters removed. If text is null, it returns null.

In plain terms, you hand it some text and it shaves the unwanted characters off both ends, leaving the middle untouched.

Example 1: Remove extra spaces around text

Strip the spaces padding both ends of a city name.

Text.Trim("     New York City     ")

Result: New York City

The spaces at the start and end are gone, while the single spaces between the words stay.

Example 2: Strip leading and trailing zeros

You can pass a character to trim instead of whitespace. Here you remove the padding zeros around a number stored as text.

Text.Trim("0000056.4200", "0")

Result: 56.42

Only the zeros at the two ends are removed. The zeros that sit between other digits are left alone.

Example 3: Remove characters wrapping a label

Clean the asterisks off both sides of a status label.

Text.Trim("***Pending***", "*")

Result: Pending

Trimming stops at the first character that is not an asterisk, so the word in the middle is kept.

Example 4: Clean a column with a list of characters

Stripping junk off both ends of a column is the most common real use of Text.Trim.

Say you have a SalesStatus query with a Property and a Status column, and the status values are wrapped in # and @ characters.

Here is the starting data:

Property	Status
1620 Ferris Way	##@@Pending@@##
757 1st Ave S	Sold
22303 Fillmore	##@@Pending@@##

Pass a list of characters so both # and @ get stripped from each end:

let
    Source = Excel.CurrentWorkbook(){[Name="SalesStatus"]}[Content],
    #"Trimmed Status" = Table.TransformColumns(Source, {{"Status", each Text.Trim(_, {"#", "@"}), type text}})
in
    #"Trimmed Status"

This removes any # or @ from the start and end of every value in Status.

The result keeps the clean labels:

Property	Status
1620 Ferris Way	Pending
757 1st Ave S	Sold
22303 Fillmore	Pending

The Sold row was already clean, so it comes through unchanged.

Example 5: Add a cleaned column without touching the original

Sometimes you want to keep the raw value and add a cleaned copy next to it.

Say you have a ProductListings query with an Item and a Listing column, and the listings have trailing dots.

Here is the starting data:

Item	Listing
Desk	Stapler…
Pen	Notebook..
Ink	Marker.

Add a new Clean Name column that trims the dots:

let
    Source = Excel.CurrentWorkbook(){[Name="ProductListings"]}[Content],
    #"Clean Name" = Table.AddColumn(Source, "Clean Name", each Text.Trim([Listing], "."), type text)
in
    #"Clean Name"

This builds a new column while leaving Listing as it was.

The result adds the cleaned column:

Item	Listing	Clean Name
Desk	Stapler…	Stapler
Pen	Notebook..	Notebook
Ink	Marker.	Marker

Each Clean Name value has its trailing dots removed, no matter how many there were.

Things to keep in mind with Text.Trim

It only touches the ends, never the middle. Inner spaces and characters stay (Example 1). To collapse or remove spaces between words, use Text.Replace instead.
It does not remove non-printing characters. Hidden line breaks and control characters survive Text.Trim. Use Text.Clean for those, often together with Text.Trim.
Non-text input throws an error. A number gives Expression.Error: We cannot convert the value 42 to type Text. Convert it first with Text.From.

Performance and query folding

Text.Trim is cheap and runs well inside Table.TransformColumns or Table.AddColumn over a column.

It usually evaluates locally rather than folding back to the source, which is fine for the row counts you meet in typical Excel and Power BI work.

Common questions about Text.Trim

Can Text.Trim remove the spaces between words?

No, it only works on the two ends. For spaces inside the text, use Text.Replace([Column]," ","") to strip them out.

What is the difference between Text.Trim and Text.Clean?

Text.Trim removes characters from the ends, with whitespace as the default. Text.Clean removes non-printing control characters from anywhere in the text. They are often used together.

List of All Power Query Functions

Related Power Query Functions / Articles: