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 textDescription
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.
Consider a situation where you need a text string to be a precise length, say, nine characters. If you leave the third argument of the function blank, the function will automatically append spaces to the end of your text string.
Let’s take the word ‘Gorilla’ as an example, which is originally seven characters long. To extend its length to nine characters using Text.PadEnd, we would use:
Text.PadEnd( "Gorilla", 9 ) // Returns "Gorilla "As you can see, the function has added two spaces to the end of ‘Gorilla’ to make it nine characters long.
While padding with spaces is an option, you might more commonly find yourself adding trailing zeroes to the end of numerical strings. This comes in handy when you’re working with data like invoice numbers or product codes, where you might need a seven-character string, padded with zeroes at the end if it falls short.
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" Each time, our function appends zeroes to the end of the string until it’s seven characters long.
But don’t limit yourself to just spaces and zeroes! The Text.PadEnd function can extend your string with any character you specify. Let’s say we want to extend the string “Rick” with the “|” character until it’s ten characters long. Here’s how we do it:
Text.PadEnd( "Rick", 10, "|" ) // Returns "Rick||||||"
As you can see, with the Text.PadEnd function, you can handle a wide variety of text formatting tasks. Understanding how to use this function will prove helpful in your Power Query data transformations.
Related articles
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
Related functions
Other functions related to Text.PadEnd are:
