Text.PadStart - Text Function

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

The Text.PadStart function 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.

Padding with Spaces

Imagine you have the word “Gorilla” but need it to be nine characters long. If you don’t specify the padding character in the third argument, the function defaults to using spaces. For example:

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

The function pads the word with two spaces at the start to make it nine characters long.

Padding with Leading Zeroes

Padding text with spaces is straightforward, but more often, you might need to add leading zeroes to maintain uniform data length. This is useful for 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, the function pads the number with zeroes at the start until it is seven characters long.

Padding with Custom Characters

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. 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 transformations.

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, they can 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

Other functions related to Text.PadStart are:

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