Text.TrimStart Function in Power Query M (4 Easy Examples)

Text.TrimStart removes all leading characters from a text value and returns what is left. By default it strips leading whitespace. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to clean up leading spaces, strip the zero-padding from IDs, or remove junk prefixes from a column, this is the function you reach for.

Table of Contents

Syntax of Text.TrimStart Function

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

where

text (required, nullable text). The text value to remove leading characters from.
trim (optional, any). Overrides the whitespace characters trimmed by default. Either a single character or a list of single characters. Trimming stops at the first character not in this set.

Returns: a nullable text value, the input with all leading whitespace (or all leading occurrences of the trim characters) removed. Trailing characters are not touched. If text is null, it returns null.

In plain terms, you tell it which characters count as junk, and it deletes them from the left until it hits a real character.

Example 1: Remove leading spaces from a text value

Clean up a report title that came in with extra spaces on both sides.

let
    Source = Text.TrimStart("     Inventory Report     ")
in
    Source

Result: "Inventory Report "

The five leading spaces are gone, but the five trailing spaces are still there. Text.TrimStart only cleans the left side.

Example 2: Strip leading zeros from an ID

Zero-padded IDs are the most common reason to use the second argument. Pass "0" to remove the padding.

let
    Source = Text.TrimStart("0000875","0")
in
    Source

Result: "875"

The result is still text, not a number. If you need an actual number, wrap it in Number.FromText or change the column type afterwards.

Example 3: Trim a list of characters

Email subjects often arrive with reply markers like >> stuck to the front. Pass a list to trim more than one character.

let
    Source = Text.TrimStart(">> Action needed: renew license",{">"," "})
in
    Source

Result: "Action needed: renew license"

One thing to watch: a custom trim argument replaces the default whitespace set, it does not add to it. The list here includes " ", otherwise trimming would stop at the space after >>.

Example 4: Clean a whole column of leading characters

In a real query, you apply Text.TrimStart to every row of a column with Table.TransformColumns.

Say you have a TaskList query where the tasks were pasted in with stray dashes and spaces at the start.

Here is the starting data:

Task	Owner
– – Update payroll sheet	Dana
–Approve vendor invoices	Raj
– Submit tax forms	Mei

Now trim any mix of dashes and spaces from the Task column:

let
    Source = Excel.CurrentWorkbook(){[Name="TaskList"]}[Content],
    #"Cleaned Task" = Table.TransformColumns(Source, {{"Task", each Text.TrimStart(_, {"-", " "}), type text}})
in
    #"Cleaned Task"

The underscore _ stands for the current cell value, and type text keeps the column typed.

The result has clean task names:

Task	Owner
Update payroll sheet	Dana
Approve vendor invoices	Raj
Submit tax forms	Mei

Every leading dash and space is stripped, no matter how they were mixed. Trimming keeps going as long as the next character is in the list.

Things to keep in mind with Text.TrimStart

Trimming stops at the first non-trim character, so characters inside the text are safe. Text.TrimStart("0070905","0") returns "70905". The zero in the middle stays.
trim takes single characters, not prefixes. A list like {"-"," "} strips any mix of those characters, not the literal sequence "- ". To remove an exact prefix, test with Text.StartsWith and cut it with Text.RemoveRange.
Character ranges work in the trim list. {"0".."9"} strips any leading digits and {"a".."z"} any leading lowercase letters. Handy when the junk varies.
An input made entirely of trim characters returns "", not null. Text.TrimStart("0000","0") gives an empty string, so a null check will not catch it.
The default whitespace set does not include the non-breaking space (character 160), which is common in web data. Run Text.Replace(x,Character.FromNumber(160)," ") first if leading spaces refuse to trim.

Common questions about Text.TrimStart

What is the difference between Text.TrimStart, Text.TrimEnd, and Text.Trim?

Text.TrimStart removes leading characters only, Text.TrimEnd removes trailing characters only, and Text.Trim cleans both ends. All three take the same optional trim argument.

List of All Power Query Functions

Related Power Query Functions / Articles: