Text.Trim - Text Function

Text.Trim is a Power Query M function that removes leading and trailing whitespace from a text value. The function returns the modified text value without the extra whitespace.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Description

The Text.Trim function trims off excess characters from the beginning and end of your text. By default, Text.Trim removes whitespace, but you can specify other characters to trim as well.

Examples

Let’s explore some examples to understand how Text.Trim works.

Trimming Whitespace

If we have a string like this: ” gorilla “, it’s surrounded by extra spaces. Using Text.Trim without specifying a character to trim defaults to trimming whitespace.

Text.Trim( "  gorilla  " ) // Output: "gorilla".

Specifying a Character to Trim

You can also specify a character to trim. For example, let’s say you want to remove any leading or trailing hyphen:

Text.Trim( "- -gorilla- -", "-" )     // Output: " -gorilla- "
Text.Trim( "--- -gorilla- ---", "-" ) // Output: " -gorilla- "

Notice that it only removes the hyphens at the very beginning and end of the string, not those surrounding the word “gorilla”.

Trimming Multiple Characters

You can also specify multiple characters to trim by passing them as a list. For instance:

Text.Trim( "- -gorilla- - ", { "-", " " } ) // Output: "gorilla"

Here, it removes both hyphens and spaces from the beginning and end of the string.

In summary, the Text.Trim function in Power Query M is useful to clean up your text by removing unwanted characters from the beginning and end. Whether it’s extra spaces, unnecessary punctuation, or other characters, you can use Text.Trim to remove them all.

Learn more about Text.Trim in the following articles:

Text Functions in Power Query M (150+ Examples)
Your guide to Text Functions in Power Query M. Learn from practical examples and master Power Query’s most useful Text functions. » Read more
Removing Excess Spaces Between Words in Power Query
This article will showcase three effective methods to removing spaces between words gracefully. We’ll delve into strategies from creating recursive functions with the ‘@’ operator to employing ‘List.Generate’ for iterative replacements. We’ll also explore a unique approach to splitting and combining text values. » Read more

Other functions related to Text.Trim are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/text-trim