Text.Format in Power Query: build dynamic text cleanly
How-to · By Tahir Nas · Examples run in our simulator · Updated
8 min read
Text.Format builds a piece of text from a template with placeholders. #{0}, #{1} and so on take values from a list by position, and #[Name] takes values from a record by field name. It reads better than a chain of & operators and converts numbers and dates to text for you. A null value leaves a gap instead of turning the whole result into null.
The two placeholder styles
Microsoft's Text.Format reference gives the signature as Text.Format(formatString, arguments, optional culture), where arguments is a list or a record. Its first example uses a list:
let
Result = Text.Format("#{0}, #{1}, and #{2}.", {17, 7, 22})
in
ResultResult
17, 7, and 22.
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
That returns the text 17, 7, and 22. (full stop included). Every example here was run in the site's M simulator, and the results quoted are the ones it returned. The numbers in the braces are positions in the list, starting at 0, and you can use the same position more than once: Text.Format("#{0} and #{0} again", {"echo"}) gives "echo and echo again".
The second style uses field names. #[city] reads the field city from a record. In a custom column the current row is already a record, called _, so you can pass it straight in and refer to your columns by name:
let
Source = #table(type table [Customer = text, Invoice = text, Amount = number, Due = date], {
{"Kite Co", "INV-104", 1250.5, #date(2026, 11, 30)},
{"Reel Ltd", "INV-105", 89, #date(2026, 12, 15)}
}),
Reminder = Table.AddColumn(Source, "Reminder",
each Text.Format("#[Customer]: invoice #[Invoice] for #[Amount] is due on #[Due].", _, "en-GB"),
type text)
in
ReminderResult
| Customer | Invoice | Amount | Due | Reminder |
|---|---|---|---|---|
| Kite Co | INV-104 | 1250.5 | 2026-11-30 | Kite Co: invoice INV-104 for 1250.5 is due on 30/11/2026. |
| Reel Ltd | INV-105 | 89 | 2026-12-15 | Reel Ltd: invoice INV-105 for 89 is due on 15/12/2026. |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The first reminder reads "Kite Co: invoice INV-104 for 1250.5 is due on 30/11/2026." The amount and the date were converted to text without any Text.From calls, and the date uses the British day-first order because of the "en-GB" culture. If _ and each are new to you, the glossary entry for each explains both in a paragraph.
Why it beats a chain of &
The & operator joins text, but the M specification's table for the combination operator shows that text combined with null gives null. One missing middle name and the whole full name disappears. Here are the three common ways to build a name, side by side:
let
Source = #table(type table [First = text, Middle = nullable text, Last = text], {
{"Ada", null, "Lovelace"},
{"Grace", "B", "Hopper"}
}),
WithAmpersand = Table.AddColumn(Source, "With &", each [First] & " " & [Middle] & " " & [Last]),
WithFormat = Table.AddColumn(WithAmpersand, "With Text.Format",
each Text.Format("#{0} #{1} #{2}", {[First], [Middle], [Last]})),
WithCombine = Table.AddColumn(WithFormat, "With Text.Combine",
each Text.Combine({[First], [Middle], [Last]}, " "))
in
WithCombineResult
| First | Middle | Last | With & | With Text.Format | With Text.Combine |
|---|---|---|---|---|---|
| Ada | null | Lovelace | null | Ada Lovelace | Ada Lovelace |
| Grace | B | Hopper | Grace B Hopper | Grace B Hopper | Grace B Hopper |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
For Ada, whose middle name is null, the & column returns null. Text.Format returns "Ada Lovelace" with the empty slot left as two spaces, and Text.Combine, which ignores nulls, returns "Ada Lovelace". For Grace, all three agree on "Grace B Hopper". Going the other way, splitting full names into first and last names handles middle names and titles.
So the rule of thumb is: use Text.Combine when you are joining a list of parts with one separator and want nulls skipped, and Text.Format when the text around the values matters, as in a sentence, a label or a file name. Wrap a placeholder that may be empty in a check if the double space would matter. The lesson on combining text and handling missing parts covers these choices, and the exercise to clean names and build a full name practises them.
Control how numbers and dates look
Text.Format converts values with the culture's default format. That is fine for whole numbers, but it won't add thousands separators or pad to two decimals. When the output is going in front of people, format each value first and pass the resulting text in:
let
Source = #table(type table [Customer = text, Amount = number, Due = date], {
{"Kite Co", 1250.5, #date(2026, 11, 30)},
{"Reel Ltd", 89, #date(2026, 12, 15)}
}),
Reminder = Table.AddColumn(Source, "Reminder",
each Text.Format("#{0} owes £#{1}, due #{2}.", {
[Customer],
Number.ToText([Amount], "N2", "en-GB"),
Date.ToText([Due], [Format = "d MMMM yyyy", Culture = "en-GB"])
}),
type text)
in
ReminderResult
| Customer | Amount | Due | Reminder |
|---|---|---|---|
| Kite Co | 1250.5 | 2026-11-30 | Kite Co owes £1,250.50, due 30 November 2026. |
| Reel Ltd | 89 | 2026-12-15 | Reel Ltd owes £89.00, due 15 December 2026. |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
This returns "Kite Co owes £1,250.50, due 30 November 2026." and "Reel Ltd owes £89.00, due 15 December 2026." N2 is a standard numeric format (number with two decimals and group separators) described on the Number.ToText page, and the Date.ToText page documents the Format and Culture fields of the options record.
The culture argument
The third argument to Text.Format decides how unformatted numbers and dates are written. The same template gives three different results:
let
RunDate = #date(2026, 3, 10),
Results = #table({"Culture", "Text"}, {
{"en-US", Text.Format("Run on #{0}, #{1} km", {RunDate, 10.5}, "en-US")},
{"en-GB", Text.Format("Run on #{0}, #{1} km", {RunDate, 10.5}, "en-GB")},
{"de-DE", Text.Format("Run on #{0}, #{1} km", {RunDate, 10.5}, "de-DE")}
})
in
ResultsResult
| Culture | Text |
|---|---|
| en-US | Run on 3/10/2026, 10.5 km |
| en-GB | Run on 10/03/2026, 10.5 km |
| de-DE | Run on 10.03.2026, 10,5 km |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
US English gives "Run on 3/10/2026, 10.5 km", British English "Run on 10/03/2026, 10.5 km" and German "Run on 10.03.2026, 10,5 km", with a decimal comma. If you leave the argument out, the conversion follows the current culture of the application running the query, which M reports through Culture.Current. That is how a report can look right on your laptop and different after someone else refreshes it. Set the culture explicitly. The lesson on converting dates and numbers with a locale explains cultures from the other direction, reading text into values.
Useful patterns
File names and keys
Building a file name or a period key is a small job where a template is easier to check than concatenation:
let
Year = 2026,
Month = 3,
FileName = Text.Format("sales_#{0}-#{1}.csv", {Year, Text.PadStart(Text.From(Month), 2, "0")})
in
FileNameResult
sales_2026-03.csv
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The result is "sales_2026-03.csv". Text.PadStart adds the leading zero so the files sort in month order. The post on keeping or removing leading zeros covers padding in more depth.
Templates stored in a table
Because the format string is just text, it can come from data. Keep message templates in a small table (an Excel table the business can edit, say), join them to your rows and fill each one from its own row:
let
Templates = #table(type table [Kind = text, Template = text], {
{"Overdue", "#[Customer] is #[Days] days overdue on #[Invoice]."},
{"Upcoming", "#[Invoice] for #[Customer] is due in #[Days] days."}
}),
Invoices = #table(type table [Invoice = text, Customer = text, Kind = text, Days = number], {
{"INV-201", "Kite Co", "Overdue", 12},
{"INV-202", "Reel Ltd", "Upcoming", 5}
}),
Joined = Table.NestedJoin(Invoices, {"Kind"}, Templates, {"Kind"}, "T", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Joined, "T", {"Template"}),
Message = Table.AddColumn(Expanded, "Message", each Text.Format([Template], _), type text),
Result = Table.SelectColumns(Message, {"Invoice", "Message"})
in
ResultResult
| Invoice | Message |
|---|---|
| INV-201 | Kite Co is 12 days overdue on INV-201. |
| INV-202 | INV-202 for Reel Ltd is due in 5 days. |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The messages read "Kite Co is 12 days overdue on INV-201." and "INV-202 for Reel Ltd is due in 5 days." The days figure would come from an ageing step like the one in the aged receivables report. Each template uses different fields in a different order, and the query doesn't change when someone rewords one. The guide to mapping tables versus nested if makes the wider case for keeping rules like these in a table.
When Text.Format raises an error
A placeholder with nothing to fill it is an error, not an empty string. Ask for position 2 from a list of two items and the query stops:
let
Result = Text.Format("#{0} has #{1} rows and #{2} errors", {"Sales", 120})
in
ResultResult: an error
Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The message is "There weren't enough elements in the enumeration to complete the operation", the same one covered on the not enough elements error page. With the record style, a #[Rep] placeholder when the row has no Rep column gives the "field of the record wasn't found" error instead, explained on the field not found error page.
Both errors are useful: they tell you the template and the data have drifted apart. If you'd rather get a fallback in a particular column, wrap the call in try … otherwise or try … catch, as described in try … catch in Power Query M, and keep the fallback obvious, such as "(template error)", so nobody mistakes it for a real message.
Text.Format at a glance
| Need | Write |
|---|---|
| Fill by position | Text.Format("#{0} / #{1}", {a, b}) |
| Fill from the current row | Text.Format("#[Customer] / #[Region]", _) |
| Fix the date and number style | Third argument, for example "en-GB" |
| Two decimals and separators | Pass Number.ToText(x, "N2", "en-GB") |
| A custom date layout | Pass Date.ToText(d, [Format = "d MMM yyyy", Culture = "en-GB"]) |
| Join parts and skip nulls | Use Text.Combine(list, " ") instead |
The text functions cheat sheet lists the neighbouring functions, and the text transformation exercises on Power Query Exercises let you practise building columns like these with your answer checked against the expected table.
Frequently asked questions
Can I mix #{0} and #[Name] in one template?
No. Microsoft documents the arguments as a list or a record. Positional placeholders read from a list and named ones read from a record, so one call can fill only one style. Pick the style that matches the data you have: a row record in a custom column, or a short list for anything else.
Does Text.Format work in both Excel and Power BI?
Yes. It is part of the M standard library, which Power Query in Excel and Power BI both use.
Sources
- Text.Format reference (learn.microsoft.com)
- combination operator (learn.microsoft.com)
- Text.Combine (learn.microsoft.com)
- Number.ToText page (learn.microsoft.com)
- Date.ToText page (learn.microsoft.com)
- Culture.Current (learn.microsoft.com)
About the author
Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.
Keep reading
- 10 Power Query habits that keep queries easy to maintainTen Power Query best practices that keep queries readable and refresh-safe: step names, types, early filters, mapping tables, parameters, checks and more.
- Cleaning phone numbers and postcodes with Power QueryStandardise UK phone numbers and postcodes in Power Query: keep only digits, fix +44 and lost leading zeros, re-space postcodes and flag values that fail.
- Fuzzy matching in Power Query: standardise inconsistent namesStandardise inconsistent product names in Power Query: normalise text first, let fuzzy merge suggest matches, then keep checked matches in a mapping table.
Practise Power Query in the browser with graded exercises checked against fresh data.
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.