Text.PadStart is a Power Query M function that pads a text value by inserting spaces or a specified character at the beginning 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.PadStart(
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 start 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.PadStart function in Power Query is useful for formatting text data. It adds extra characters to the beginning of a text value until it reaches a specified length. Let’s learn more about this function through some hands-on examples.
Imagine a scenario where you have a string of text that needs to be a certain length. For instance, say we have the word ‘Gorilla’, but we need it to be nine characters long.
If we don’t specify what to pad with in the third argument of the function, it’ll default to using a space. So, when we input ‘Gorilla’ into our function:
Text.PadStart( "Gorilla", 9 ) // Returns " Gorilla"Our function pads the word with two spaces at the start to make it nine characters long.
Padding text with spaces is quite simple, but not something you’ll find yourself doing very often. More frequently, you might find yourself adding leading zeroes to maintain uniform data length. This comes in handy when dealing with things like identification numbers, postal codes, or product codes, which often need to be a specific length.
Consider these examples:
Text.PadStart( "5440", 7, "0" ) // Returns "0005440"
Text.PadStart( "544", 7, "0" ) // Returns "0000544"
Text.PadStart( "54", 7, "0" ) // Returns "0000054" In each case, our function pads the number with zeroes at the start until it’s seven characters long.
But don’t limit yourself to just spaces and zeroes. The Text.PadStart function can pad your text with any character you specify. Let’s say we want to pad the name “Rick” with the “|” character until it’s ten characters long. Here’s how we do it:
Text.PadStart( "Rick", 10, "|" ) // Returns "||||||Rick"
As you can see, the Text.PadStart function is not only easy to use but also very useful. It lets you handle a wide range of text formatting tasks with ease.
Related articles
Learn more about Text.PadStart in the following articles:
- 3 Ways to Add Leading Zeros to Numbers in Power Query
Leading zeros help when you need a fixed value length. Yet, theycan also help when sorting text values that include numbers. This post … » Read more - 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.PadStart are:
