Downloads
Power Query MTextBeginner

Text.PadEnd

Adds characters to the end of a text value until it reaches a set length.

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

Returns: The padded text, or the original text if it is already long enough, or null when the input is null.

What it does

Text.PadEnd fills a value to a fixed width by adding characters at the end. It is the usual step when a bank or payroll system needs a fixed width text file, where every field must be the same length whatever the content. The padding character defaults to a space.

Syntax and arguments

Example

A bank wants a fixed width vendor payment file where the payee name always fills 20 characters. The team pads each name to the full width before writing the file.

let
    Payees = #table(type table [Payee = text], {{"Acme Supply"}, {"Blue Harbor"}}),
    Fixed = Table.AddColumn(Payees, "PayeeField", each Text.PadEnd([Payee], 20), type text),
    WithLength = Table.AddColumn(Fixed, "Width", each Text.Length([PayeeField]), Int64.Type)
in
    WithLength

Every PayeeField has a width of 20. "Acme Supply" and "Blue Harbor" are both 11 characters, so each is followed by nine spaces. The Width column confirms the result.

Example: a second, different use

Pad a short text code with a dot to a fixed width so a report lines up in columns.

Things to watch

In business terms

Many banks, payroll providers and tax portals still take fixed width files. Getting every field to the exact width is the difference between a file that loads and one that is rejected at the bank's gate.

Related functions

All Power Query M functions · View as Markdown · M basics