Text.TrimEnd

One of the most commonly used functions in M is Text.TrimEnd. This function allows you to remove a specific character or set of characters from the end of a string. In this article, we will explore the M code behind the Text.TrimEnd function and how you can use it to manipulate your data.

Understanding Text.TrimEnd Function

The Text.TrimEnd function is used to remove a specific set of characters from the end of a string. This function takes two arguments, the first is the string that you want to remove characters from, and the second is the set of characters that you want to remove.

For example, if you want to remove the letter “a” from the end of the string “banana,” you would use the Text.TrimEnd function as follows:

The output of this function would be the string “banan.”

The M Code Behind Text.TrimEnd

The M code behind the Text.TrimEnd function is quite simple. When you use the Text.TrimEnd function, Power Query generates the following M code:

Let’s break down this code and understand what each line does.

The first line defines the function and its arguments. The Text argument is the string that you want to remove characters from, and TrimChars is the set of characters that you want to remove. The second argument is optional, which means that if you don’t specify anything, it will default to removing whitespace characters.

The second line defines a variable called TrimCharsValue. This variable checks whether the TrimChars argument is null. If it is null, it sets TrimCharsValue to {” “}, which is a list that contains only a single space character. If TrimChars is not null, it converts the TrimChars string to a list of characters.

The third line defines a variable called LastNonTrimChar. This variable uses the List.PositionOf function to find the position of the last character in the string that is not in the TrimCharsValue list. It starts searching from the end of the string (hence the use of List.Reverse function) and stops as soon as it finds a character that is not in TrimCharsValue.

The fourth line defines a variable called TrimmedText. This variable uses the Text.Start function to extract a substring from the beginning of the original string up to the position of LastNonTrimChar. This effectively removes all the characters from the end of the string that are in the TrimCharsValue list.

Finally, the last line returns the TrimmedText variable, which is the result of the Text.TrimEnd function.

Using Text.TrimEnd Function in Power Query

Now that you understand how the Text.TrimEnd function works and what M code it generates, let’s see some examples of how you can use it in Power Query.

Example 1: Removing Trailing Spaces

One of the most common use cases for the Text.TrimEnd function is to remove trailing spaces from a string. Trailing spaces are whitespace characters that appear at the end of a string and can cause issues when you’re trying to group or sort data.

To remove trailing spaces from a string in Power Query, you can use the Text.TrimEnd function with an empty string as the second argument. For example:

The output of this function would be the string “hello world”.

Example 2: Removing Trailing Characters

You can also use the Text.TrimEnd function to remove specific characters from the end of a string. For example, if you have a list of file names that all end with the “.txt” extension, you can use the Text.TrimEnd function to remove the extension from the file names.

The output of this function would be the string “file”.

Example 3: Removing Trailing Numbers

Another use case for the Text.TrimEnd function is to remove trailing numbers from a string. For example, if you have a list of product names that all end with a number that represents the size of the product, you can use the Text.TrimEnd function to remove the size from the product names.

The output of this function would be the string “small widget”.

The Text.TrimEnd function is a powerful tool that allows you to remove specific characters from the end of a string. It’s easy to use and generates simple M code that you can customize to fit your specific needs. By understanding the M code behind the Text.TrimEnd function, you can gain a deeper understanding of how Power Query works and how you can use it to manipulate your data.

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

Upcoming Courses

Contact Us