Downloads
Power Query MTextBeginner

Text.Trim

Removes spaces, tabs and line breaks from the start and end of a text value.

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

Returns: The text without leading and trailing spaces, or null when the input is null.

What it does

Bank and ERP exports often carry stray spaces at the start or end of a code or a name. "1000 " and "1000" look the same on a report, but they do not match in a merge, a filter or a lookup. Text.Trim removes those outer spaces so values compare cleanly. It leaves spaces between words alone.

Syntax and arguments

Example

A payables team receives a vendor list with padded names. The clean up runs before the merge with the vendor master, so every payment finds its vendor.

let
    Vendors = #table(
        type table [VendorName = text],
        {{"  Acme Supply Co  "}, {"Blue Harbor LLC "}, {"  Northwind Traders"}}
    ),
    Cleaned = Table.TransformColumns(Vendors, {{"VendorName", Text.Trim, type text}})
in
    Cleaned

Each name comes back without its outer spaces. " Acme Supply Co " becomes "Acme Supply Co", and the space inside "Northwind Traders" is kept.

Example: a second, different use

A reference code that a system exported with a trailing period can be cleaned by naming the character to remove: Text.Trim("4010.", ".") returns "4010".

Things to watch

In business terms

Cleaning vendor, customer and account codes before a merge is the first step in most payables, receivables and reconciliation queries. A payment for "Acme " that fails to match "Acme" ends up on the unmatched list at month end, where it costs someone an afternoon to find.

Related functions

All Power Query M functions · View as Markdown · M basics