Text.PadEnd

What is Text.PadEnd?

The Text.PadEnd function is used to add padding characters to the right side of a text string. This is useful when you want to align text columns or when you want to add a certain number of characters to the end of a string. The function takes three arguments:

– Text: The text string to which padding characters will be added.

– Length: The total length of the resulting string after padding.

– Character: The character to be used for padding.

For example, if we want to add 5 spaces to the end of the text string “hello”, we can use the following formula:

This will result in the string “hello ” (with 5 spaces added to the end).

The M Code Behind Text.PadEnd

The Text.PadEnd function is implemented in M code, which is the programming language used in Power Query. The M code behind the function is quite simple:

Let’s break down the code line by line:

– The “let” keyword is used to define a variable called “PadEnd”. This variable is a function that takes three arguments: Text, Length, and Character.

– The function definition is followed by the “=>” symbol, which indicates the start of the function body.

– The “let” keyword is used again to define a variable called “Padding”. This variable is assigned the result of the Text.Repeat function, which repeats the “Character” string a certain number of times (equal to “Length” minus the length of the “Text” string).

– The “Result” variable is defined as the concatenation of the “Text” and “Padding” strings using the “&” operator.

– The “in” keyword is used to indicate the end of the function body and the return value of the function (which is the “Result” variable).

– Finally, the “PadEnd” function is defined and returned using the “in” keyword.

Example Usage of Text.PadEnd

Let’s look at some examples of how to use the Text.PadEnd function in Power Query.

Example 1: Padding Numbers

Suppose we have a column of numbers that we want to pad with leading zeros to make them all the same length. We can use the Text.PadEnd function for this purpose. For example, suppose we have the following table:

We can pad the numbers with leading zeros to make them all four digits long using the following formula:

This will result in the following table:

Example 2: Aligning Text Columns

Suppose we have a table with two columns of text and we want to align the text in the second column to the right. We can use the Text.PadEnd function for this purpose. For example, suppose we have the following table:

We can align the values in the second column to the right using the following formula:

This will result in the following table:

The Text.PadEnd function is a powerful tool for adding padding characters to the end of a text string in Power Query. With the knowledge of the M code behind this function, you can customize and extend its functionality to suit your specific needs.

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

Upcoming Courses

Contact Us