Text.Format
Builds a text message by putting values into numbered placeholders.
Text.Format(formatString as text, arguments as any, optional culture as nullable text) as textReturns: 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
- formatString: the template. Placeholders look like #{0}, #{1} and so on.
- arguments: a list of values, in order. Use a single value when there is only one placeholder.
- culture (optional): the culture used when a value is a number or date that has to be formatted.
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
- Every placeholder needs a matching value in the list, in the same order.
- Numbers and dates are not formatted by default. Convert them with Text.From, Number.ToText or Date.ToText first, so the output is predictable.
- The list and the placeholders must line up. Test the template on one row before you run it on the full table.
- Keep the template text in a parameter or a clear step, so a change to wording is easy to find.
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.