Power QueryExercises

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
    Result

Result

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
    Reminder

Result

CustomerInvoiceAmountDueReminder
Kite CoINV-1041250.52026-11-30Kite Co: invoice INV-104 for 1250.5 is due on 30/11/2026.
Reel LtdINV-105892026-12-15Reel 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
    WithCombine

Result

FirstMiddleLastWith &With Text.FormatWith Text.Combine
AdanullLovelacenullAda LovelaceAda Lovelace
GraceBHopperGrace B HopperGrace B HopperGrace 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
    Reminder

Result

CustomerAmountDueReminder
Kite Co1250.52026-11-30Kite Co owes £1,250.50, due 30 November 2026.
Reel Ltd892026-12-15Reel 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
    Results

Result

CultureText
en-USRun on 3/10/2026, 10.5 km
en-GBRun on 10/03/2026, 10.5 km
de-DERun 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
    FileName

Result

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
    Result

Result

InvoiceMessage
INV-201Kite Co is 12 days overdue on INV-201.
INV-202INV-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
    Result

Result: 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

NeedWrite
Fill by positionText.Format("#{0} / #{1}", {a, b})
Fill from the current rowText.Format("#[Customer] / #[Region]", _)
Fix the date and number styleThird argument, for example "en-GB"
Two decimals and separatorsPass Number.ToText(x, "N2", "en-GB")
A custom date layoutPass Date.ToText(d, [Format = "d MMM yyyy", Culture = "en-GB"])
Join parts and skip nullsUse 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

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

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.