Text.PadStart
Adds characters to the start of a text value until it reaches a set length.
Text.PadStart(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.PadStart fills a value to a fixed width by adding characters at the start. The classic use is restoring leading zeros to an account or check number that lost them in a spreadsheet, so that "420" becomes "000420" and matches the six digit account numbers in the ledger.
Syntax and arguments
- text: the value to pad. Convert numbers with Text.From first.
- count: the total length the result should reach.
- character (optional): the character to add. The default is a space, so pass "0" for zero padding.
Example
A spreadsheet of vendor account numbers has lost its leading zeros. The team restores them to six digits before the join with the ledger.
let
Vendors = #table(type table [Account = number, Name = text],
{{420, "Acme Supply Co"}, {10875, "Blue Harbor LLC"}, {1000, "Northwind Traders"}}),
Padded = Table.TransformColumns(
Table.TransformColumnTypes(Vendors, {{"Account", type text}}),
{{"Account", each Text.PadStart(_, 6, "0"), type text}})
in
Padded
The accounts come out as "000420", "010875" and "001000". Each is now six characters, the same width as the ledger.
Example: a second, different use
Make a voucher number of at least eight characters with zeros, so the vouchers sort in the right order as text.
Things to watch
- The default padding character is a space, so always pass "0" for numbers that need zeros.
- A value already longer than count is returned unchanged, not cut short.
- Padding fixes the display and the join. It does not make the value a number again, so convert back with Number.FromText if needed.
- Null in gives null out.
In business terms
Account numbers, check numbers and invoice sequences often lose their leading zeros when they pass through a spreadsheet or a CSV. Restoring the zeros is a routine step before any join to a ledger or a bank file.