Text.PadStart Function in Power Query M (5 Easy Examples)

If you want to make short text values a fixed width, or add leading zeros to codes and IDs, the Text.PadStart function is what you reach for.

It adds padding characters to the front of a text value until it hits the length you ask for.

Table of Contents

Syntax of Text.PadStart Function

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

where

text (required, nullable text). The text value you want to pad.
count (required, number). The target total length the result should reach.
character (optional, nullable text). The single character used for padding. Leave it out and a space is used by default.

Returns: the text padded at the start until it reaches count characters. If the text is already that long or longer, it comes back unchanged (nothing is cut off).

In plain terms, you give it a value and a width, and it sticks padding characters on the front until the value is that wide.

Example 1: Pad a value to a fixed width with spaces

Pad the word Name out to 10 characters using the default space.

Text.PadStart("Name", 10)

Result: Name (six leading spaces in front of Name, for 10 characters total).

There’s no character argument here, so Power Query fills the front with spaces until the text is 10 characters wide.

Example 2: Add leading zeros to a value

Turn 42 into a 6-character code with leading zeros.

Text.PadStart("42",6,"0")

Result: 000042

The text is 2 characters, so four 0 characters are added in front to reach a total length of 6.

Example 3: Text already at or over the width is left alone

Ask for a width of 5 on a value that is already 7 characters.

Text.PadStart("PRODUCT", 5)

Result: PRODUCT

Since the text is already longer than count, it comes back exactly as it was. Text.PadStart never truncates.

Example 4: Pad with a custom character

Use an asterisk instead of a space or zero.

Text.PadStart("4.2",6,"*")

Result: ***4.2

The third argument sets the pad character, so three * characters fill the front to reach a length of 6.

Example 5: Pad an ID column to a fixed width

Where you’ll actually use this most is on a whole column, so every code lines up at the same width.

Here is the starting data:

InvoiceCode
INV-7
INV-86
INV-432

Add a new column that pads each InvoiceCode to 8 characters with leading zeros:

let
    Source = Excel.CurrentWorkbook(){[Name="Invoices"]}[Content],
    #"Padded Codes" = Table.AddColumn(Source, "FixedWidthCode", each Text.PadStart([InvoiceCode], 8, "0"), type text)
in
    #"Padded Codes"

The result produces:

InvoiceCode	FixedWidthCode
INV-7	000INV-7
INV-86	00INV-86
INV-432	0INV-432

Each code now reaches 8 characters, so shorter codes get more zeros in front and they all line up to the same width.

Things to keep in mind with Text.PadStart

character must be exactly one character. Passing more than one throws Expression.Error: A single character was expected. Pad with one character at a time.
Numbers must be converted to text first. Text.PadStart only works on text, so wrap numeric values in Text.From (for example Text.PadStart(Text.From([Qty]),5,"0")) before padding.
It runs locally and does not fold. This is a local text transform, much like Text.Contains, so against a database source the padding happens in Power Query after the data is pulled. That is fine for normal data volumes.

Common questions about Text.PadStart

What’s the difference between Text.PadStart and Text.PadEnd?

Text.PadStart adds padding to the front (the left) of the text, which is what you want for leading zeros and right-aligned codes. Text.PadEnd adds padding to the back (the right) instead, handy for lining up labels on the left.

How do I add leading zeros to numbers?

Convert the number to text first, then pad it. For example Text.PadStart(Text.From([Number]),6,"0") turns 42 into 000042. Without the Text.From step the number can’t be padded.

List of All Power Query Functions

Related Power Query Functions / Articles: