Text.PadEnd
Adds characters to the end of a text value until it reaches a set length.
Text.PadEnd(text as nullable text, count as number, optional character as nullable text) as nullable textReturns: 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
- text: the value to pad.
- count: the length the result should reach.
- character (optional): the character to add. The default is a space.
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
- A value longer than count is returned unchanged, so the field can still be too wide for the file. Check the length after padding.
- The default padding is a space. Pass another character when the file format asks for it.
- Trim the value first with Text.Trim so the padding is not added after a stray space.
- Null in gives null out.
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.