Text.TrimStart
Removes spaces from the start of a text value only, leaving the end alone.
Text.TrimStart(text as nullable text, optional trim as any) as nullable textReturns: The text with its leading spaces removed, or null when the input is null.
What it does
Some exports indent a code or a name with leading spaces, and the indent is the only thing wrong with it. Text.TrimStart removes those leading spaces and leaves any trailing spaces in place. Use it when you know the problem is on one side only, or when a trailing space is meaningful.
Syntax and arguments
- text: the value to clean.
- trim (optional): a character or list of characters to remove from the start instead of spaces, such as "0" to strip leading zeros from an account number.
Example
An ERP export writes account numbers as text with leading zeros and spaces. The accounting team wants the bare number for a lookup.
let
Raw = " 000420",
Trimmed = Text.TrimStart(Raw),
NoZeros = Text.TrimStart(Trimmed, "0")
in
{Trimmed, NoZeros}
The first step gives "000420", removing only the spaces. The second step gives "420", removing the leading zeros as well. The result is the list {"000420", "420"}.
Example: a second, different use
Strip only the leading zeros from a voucher reference such as "00087-A" to get "87-A", while leaving the suffix alone.
Things to watch
- It only works on the start. A trailing space stays, so pair it with Text.TrimEnd when both sides matter.
- Stripping leading zeros turns "000420" into "420". Convert back with Text.PadStart if the report needs six digits.
- When the trim argument is a list, leading characters are removed for as long as they appear in the list.
- Null input gives null output.
In business terms
Leading zeros cause many failed joins between a general ledger export and a chart of accounts. Trimming one side of a code, and then padding it back to the standard width, is a routine fix at the start of a month-end mapping step.