Text.PadEnd - Text Function

Text.PadEnd is a Power Query M function that pads a text value by inserting spaces or a specified character at the end until it reaches a specified length. The function returns the padded text value.

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

Syntax

Text.PadEnd(
   text as nullable text,
   count as number,
   optional character as nullable text,
) as nullable text

Description

The Text.PadEnd function returns a text value padded to length count by inserting spaces at the end of the text value text. An optional character character can be used to specify the character used for padding. The default pad character is a space.

Examples

The Text.PadEnd function in Power Query M language allows you to lengthen a string by appending characters to the end. To help you better understand this function, let’s walk through some examples together.

Padding with Spaces

Suppose you need a text string to be a precise length, say, nine characters. If you leave the third argument of the function blank, Text.PadEnd will automatically append spaces to the end of your text string.

For example, the word “Gorilla” is seven characters long. To extend its length to nine characters using Text.PadEnd, you would use:

Text.PadEnd( "Gorilla", 9 ) // Returns "Gorilla  "

The function adds two spaces to the end of “Gorilla” to make it nine characters long.

Padding with Zeroes

While padding with spaces is common, you might also need to add trailing zeroes to numerical strings. This is useful when working with data like invoice numbers or product codes, where a string should be a specific length.

Consider the following examples:

Text.PadEnd( "5440", 7, "0" )  // Returns "5440000"
Text.PadEnd( "544",  7, "0" )  // Returns "5440000"
Text.PadEnd( "54",   7, "0" )  // Returns "5400000"

In each case, the function appends zeroes to the end of the string until it reaches seven characters.

Padding with Custom Characters

You can also extend your string with any character you specify. For instance, to extend the string “Rick” with the “|” character until it’s ten characters long, use:

Text.PadEnd( "Rick", 10, "|" ) // Returns "Rick||||||"

Text.PadStart to suffix strings with characters in Power Query M

As you can see, with the Text.PadEnd function, you can handle a wide variety of text formatting tasks. Whether you need to pad with spaces, zeroes, or any other character, understanding how to use this function will be helpful in your Power Query data transformations.

Learn more about Text.PadEnd 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

Other functions related to Text.PadEnd are:

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