Text.Trim

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Text.Trim

Understanding Text.Trim

Before we dive into the M code behind `Text.Trim`, let’s take a moment to understand what this function does. When you apply `Text.Trim` to a text value, it removes any spaces, tabs, or line breaks at the beginning or end of the value. For example, if you had a text value that looked like this:

Hello World

Applying `Text.Trim` would result in:

Hello World

This can be useful for cleaning up text values that might contain extra spaces or other whitespace characters that could cause issues with further analysis or manipulation.

The M Code Behind Text.Trim

Now let’s take a closer look at the M code behind `Text.Trim`. Here’s the basic syntax for the function:

Text.Trim(text as nullable text, optional trimChars as nullable text) as text

The first argument, `text`, is the text value that you want to trim. The second argument, `trimChars`, is an optional argument that allows you to specify additional characters that should be trimmed from the text value. By default, `Text.Trim` only removes spaces, tabs, and line breaks.

Here’s what the M code looks like for `Text.Trim`:

let

Source = (text as nullable text, optional trimChars as nullable text) as text =>

let

removeLeading = if trimChars <> null then Text.RemoveStart(text, trimChars) else Text.TrimStart(text),

removeTrailing = if trimChars <> null then Text.RemoveEnd(removeLeading, trimChars) else Text.TrimEnd(removeLeading),

result = removeTrailing

in

result

in

Source

Let’s break down this code step by step.

Step 1: Define the Function

The first line of the M code defines the function:

Source = (text as nullable text, optional trimChars as nullable text) as text =>

This creates a function called `Source` that takes two arguments: `text` and `trimChars`. `text` is a nullable text value (meaning it can be null or contain text), and `trimChars` is an optional nullable text value.

Step 2: Remove Leading Whitespace

The next line of the code checks to see if `trimChars` has been specified. If it has, it removes any leading instances of those characters from `text` using the `Text.RemoveStart` function:

removeLeading = if trimChars <> null then Text.RemoveStart(text, trimChars) else Text.TrimStart(text),

If `trimChars` is null (meaning it hasn’t been specified), the code uses the `Text.TrimStart` function instead to remove any leading whitespace characters.

Step 3: Remove Trailing Whitespace

The next line of the code removes any trailing whitespace characters from `removeLeading` using the `Text.RemoveEnd` function:

removeTrailing = if trimChars <> null then Text.RemoveEnd(removeLeading, trimChars) else Text.TrimEnd(removeLeading),

Again, if `trimChars` is null, the code uses the `Text.TrimEnd` function instead to remove trailing whitespace characters.

Step 4: Return the Result

Finally, the last line of the code returns the trimmed text value:

result = removeTrailing

This is the value that will be returned whenever you call `Text.Trim` with a text value as the argument.

Understanding the M code behind functions like `Text.Trim` can be helpful for troubleshooting issues or customizing the function to suit your specific needs. By breaking down the code into its component parts, you can see exactly how the function is working and make changes as necessary. Hopefully this article has given you a better understanding of how `Text.Trim` works in Power Query!

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)