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

Text.TrimEnd removes characters from the end of a text value, with trailing whitespace removed by default. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to strip blank spaces or a known set of characters off the end of your text while leaving the start untouched, this is the function you reach for.

Table of Contents

Syntax of Text.TrimEnd Function

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

where

text (required, nullable text). The text value you want to trim from the end.
trim (optional, any). A single character, or a list of single characters, to remove from the end. Omit it to remove trailing whitespace.

Returns: a text value with the matching characters removed from the END only. If text is null, it returns null.

In plain terms, it walks in from the right and drops characters as long as they match, stopping at the first one that does not.

Example 1: Remove trailing whitespace

Clean the trailing spaces off a heading without touching the words.

Text.TrimEnd("Quarterly Report     ")

Result: Quarterly Report

With no trim argument, the default removes the trailing spaces and keeps the text intact.

Example 2: Remove a single trailing character

Strip a stray semicolon off the end of a label.

Text.TrimEnd("Total amount due;",";")

Result: Total amount due

Passing ";" removes the trailing semicolon. Note that this replaces the whitespace default, so spaces are no longer stripped.

Example 3: Remove a set of trailing characters

Clean off a mix of trailing dots and question marks at once.

Text.TrimEnd("Invoice 2045...??",{".","?"})

Result: Invoice 2045

When you pass a list, any character in it is removed from the end. Here both . and ? are cleared until the function hits the 5.

Example 4: Strip a unit off a column of readings

Say you have a Measurements query with an Item and a Reading column, where each reading ends in the unit cm.

You want a clean numeric value with the unit removed.

Here is the starting data:

Item	Reading
Width	45cm
Height	120cm
Depth	8cm

Add a column that trims the c and m characters off the end:

let
    Source = Excel.CurrentWorkbook(){[Name="Measurements"]}[Content],
    #"Clean Reading" = Table.AddColumn(Source, "Value", each Text.TrimEnd([Reading], {"c","m"}), type text)
in
    #"Clean Reading"

This removes the trailing cm from each reading and keeps the number.

The result adds a Value column:

Item	Reading	Value
Width	45cm	45
Height	120cm	120
Depth	8cm	8

The c and m are stripped from the right, and the function stops as soon as it reaches a digit.

Things to keep in mind with Text.TrimEnd

It only trims the END. The start and middle are left alone. Use Text.TrimStart to clean the left side, or Text.Trim to clean both ends at once.
Passing trim replaces the whitespace default. Once you supply characters to remove, it no longer strips spaces. Include " " in your list if you still want spaces gone.
If every trailing character matches, you can end up with an empty string. Trimming keeps eating characters from the right until it hits a non-match, so Text.TrimEnd("000","0") returns "", not null.

List of All Power Query Functions

Related Power Query Functions / Articles: