Text.PadEnd Function in Power Query M (4 Easy Examples)

If you want to make every text value a fixed width by adding characters on the right, Text.PadEnd does exactly that. It appends a pad character to the end of the text until it reaches the length you set.

Table of Contents

Syntax of Text.PadEnd Function

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

where

text (required, nullable text). The text you want to pad on the right.
count (required, number). The total length the result should reach.
character (optional, nullable text). A single character used to pad. Defaults to a space.

Returns: a text value padded on the right to length count. If it is already that long, it is returned unchanged. If text is null, it returns null.

In plain terms, Text.PadEnd keeps adding the pad character after your text until the string hits the target length. It only ever adds characters, so it never shortens text.

Example 1 – Pad with spaces

Let’s take the word Cat and pad it out to a length of 6 using the default pad character.

let
    Source = Text.PadEnd("Cat",6)
in
    Source

Result: Cat

Since no pad character is given, Text.PadEnd uses a space. Cat is 3 characters, so it gets 3 spaces added to reach length 6. Those trailing spaces in the result are real characters, even though they look invisible.

Example 2 – Pad with a custom character

This time we pad the text 7 to length 4 using 0 as the pad character.

let
    Source = Text.PadEnd("7",4,"0")
in
    Source

Result: 7000

The third argument sets the pad character to 0. The string 7 is one character, so three zeros are added on the right to reach length 4. This is handy for building fixed-width codes.

Example 3 – Already long enough returns unchanged

Here we ask for a length of 6, but the text Spreadsheet is already longer than that.

let
    Source = Text.PadEnd("Spreadsheet",6,"-")
in
    Source

Result: Spreadsheet

Spreadsheet is 11 characters, which is more than the target length of 6. So Text.PadEnd returns it as is. It never trims or truncates text that is already long enough.

Example 4 – Pad a SKU column to a fixed width

Say you have a column of SKU codes of different lengths, and you want them all padded to width 6 with dashes.

Here is the starting data in a table named Skus.

Sku
A12
B7
C100

Now add a column that pads each SKU to length 6 using -.

let
    Source = Excel.CurrentWorkbook(){[Name="Skus"]}[Content],
    Added = Table.AddColumn(Source,"Padded",each Text.PadEnd([Sku],6,"-"),type text)
in
    Added

This gives you the following result.

Sku	Padded
A12	A12—
B7	B7—-
C100	C100–

Each SKU gets enough dashes on the right to reach length 6. Shorter codes get more dashes, and codes already at 6 characters would stay unchanged.

Things to keep in mind with Text.PadEnd

It pads on the RIGHT (the end). If you need to pad on the left instead, use Text.PadStart.
The default pad character is a space. It is invisible but it is a real character in the result.
It never truncates. If text is already as long as count or longer, you get the original text back.
The character argument must be a single character. Passing a multi-character string throws Expression.Error: The value isn't a single-character string.
It only works on text. To pad a number, convert it to text first with Text.From (or Number.ToText), like Text.PadEnd(Text.From([Num]),6,"0").
A null text value returns null.

Common questions about Text.PadEnd

What is the difference between Text.PadEnd and Text.PadStart?

Text.PadEnd adds pad characters to the right (the end) of the text. Text.PadStart adds them to the left (the start). Both stop once the string reaches the length you set, and neither one ever truncates.

Can I pad with more than one character?

No. The character argument has to be a single character. If you pass a string with two or more characters, Text.PadEnd throws Expression.Error: The value isn't a single-character string.

List of All Power Query Functions

Related Power Query Functions / Articles: