Text.PadStart

Updated on

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 text

Description

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"
Text.PadStart to prefix strings with a characters in Power Query M

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.

Learn more about Text.PadStart in the following articles:

Other functions related to Text.PadStart are:

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