Downloads
Power Query MTextBeginner

Text.PadStart

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

Syntax
Text.PadStart(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.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

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

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.

Related functions

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