Learn how Text.Trim, Text.Upper, and Text.Lower clean up text values, why Text.Trim doesn't always catch what looks like whitespace, and when case-insensitive comparison is the better fix than converting case at all.

Text.Trim(), Text.Upper() & Text.Lower()

These three are the most-used text cleanup functions in Power Query — removing stray whitespace and normalizing case before a value is compared, grouped, or joined against another source.

Text.Trim(text as text, optional trimChars as any) as text
Text.Upper(text as text) as text
Text.Lower(text as text) as text

Basic Example

#"Trimmed" = Table.TransformColumns(Source, {{"Name", Text.Trim}})

"  Alice  " -> "Alice"

Passed as a bare function reference to Table.TransformColumns(), Text.Trim runs once per value with no extra arguments needed — the default behavior trims standard spaces, tabs, and line breaks from both ends.

Text.Trim Doesn't Catch Everything That Looks Like Whitespace

A non-breaking space (Unicode 00A0) — common in data copy-pasted from a web page or exported from certain legacy systems — looks identical to a normal space but isn't one, and plain Text.Trim() leaves it in place.

"1234 "     <- trailing normal space, Text.Trim removes it
"1234 " <- trailing non-breaking space, Text.Trim does NOT remove it

Try it live

·sp

Text.Trim("1234 ")

Result:"1234"— 5 characters in, 4 characters out

Fix: replace the non-breaking space explicitly before trimming.

each Text.Trim(Text.Replace(_, "#(00A0)", " "))

See We Couldn't Convert to Number (or Date) for this exact pattern in the context of a type-conversion error it silently causes.

Text.Upper / Text.Lower for Comparison, Not Just Display

each Text.Lower([Status]) = "active"

Comparisons in M are case-sensitive by default — "Active" and "active" don't match. Converting both sides of a comparison to a consistent case is the standard fix, covered in more depth in Table.SelectRows().

The Optional trimChars Argument

Text.Trim("**Featured**", {"*"})

"**Featured**" -> "Featured"

Supplying a list of characters trims those specific characters from both ends instead of whitespace — useful for stripping a consistent wrapping character (asterisks, quotes) that a source system adds around certain values.

Common Mistakes

Assuming Text.Trim Handles Every Whitespace-Like Character

As covered above — non-breaking spaces are the most common exception, but any Unicode character that merely looks like whitespace in a preview grid needs its own explicit Text.Replace() before Text.Trim() will catch it.

Overwriting the Original Case When Only Comparison Needed It

#"Changed Case" = Table.TransformColumns(Source, {{"CustomerName", Text.Upper}})

Doing this to enable a case-insensitive match elsewhere permanently destroys the original casing in the output — if the value still needs to display in its original form, convert case only inside the comparison expression itself (Text.Lower([Status]) = "active"), not as a standing transformation of the column.

Using Text.Upper/Text.Lower for Proper Case

Neither function produces "Title Case" or "Proper Case" (capitalizing just the first letter of each word) — that needs Text.Proper(), a related but different function, not a combination of Text.Upper and Text.Lower.

Next Steps

M Language
Table.SelectRows()
Table.TransformColumns()
TRIM(), UPPER() & LOWER() in DAX — DAX's TRIM() also collapses internal spaces
M Function Reference

Text.Trim(), Text.Upper() & Text.Lower()

On this page