Text.Trim
Removes spaces, tabs and line breaks from the start and end of a text value.
Text.Trim(text as nullable text, optional trim as any) as nullable textReturns: 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
- text: the value to clean. Inside Table.TransformColumns you pass the column's value by name, as the example shows.
- trim (optional): a character, or a list of characters, to remove instead of whitespace. For example "." or {".", "#"}.
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
- Text.Trim removes ordinary whitespace. A value that still fails to match after trimming may hold a non-breaking space or another hidden character. Check it with Text.Length and Text.ToList.
- It trims both ends. Use Text.TrimStart or Text.TrimEnd to trim only one side.
- Trimming does not make a comparison ignore case. "Acme" and "ACME" still differ.
- A null in gives a null out, so later steps must allow for nulls.
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.