Downloads
Power Query MTextIntermediate

Text.Format

Builds a text message by putting values into numbered placeholders.

Syntax
Text.Format(formatString as text, arguments as any, optional culture as nullable text) as text

Returns: The message with each placeholder replaced by its value.

What it does

Text.Format fills a template with values. A collections team uses it to build a reminder line from a template, such as "Invoice #{0} is #{1} days past due", without joining many pieces by hand. The placeholders are numbered from zero, and the values are given as a list in the same order.

Syntax and arguments

Example

A collections report builds a reminder sentence for each overdue invoice from the invoice number and the days past due.

let
    Overdue = #table(type table [Invoice = text, DaysLate = number],
        {{"INV1043", 44}, {"INV1051", 12}}),
    Reminders = Table.AddColumn(Overdue, "Reminder",
        each Text.Format("Invoice #{0} is #{1} days past due.", {[Invoice], Text.From([DaysLate])}),
        type text)
in
    Reminders

The first row reads "Invoice INV1043 is 44 days past due." and the second reads "Invoice INV1051 is 12 days past due." The template stays in one place, so a wording change is made once.

Example: a second, different use

Build a label for a file such as "Bank statement for #{0}, period #{1}" with the account and the period.

Things to watch

In business terms

Reminder letters, audit requests, email subject lines and report titles are built from a template and a set of values. Text.Format keeps that wording in one place and fills it for every row.

Related functions

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