Text.Format Function in Power Query M (5 Easy Examples)

Text.Format builds a text string by filling placeholders in a format string with values you supply. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to stitch values into a sentence or label using a reusable template instead of gluing strings together by hand, this is the function you reach for. You write the wording once with placeholders, then pass in the values that fill them.

Table of Contents

Syntax of Text.Format Function

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

where

formatString (required, text). The template text holding the placeholders to fill. Use #{0}, #{1} and so on for positions in a list, or #[fieldName] for fields in a record.
arguments (required, any). The values that fill the placeholders. Pass a list when the template uses indexed #{0} placeholders, or a record when it uses named #[field] placeholders.
culture (optional, nullable text). A culture name such as "en-US" that controls how dates and numbers are formatted. Omit it to use the current environment culture.

Returns: a text value, the format string with every placeholder replaced by its matching value.

The placeholder style depends on what you pass as arguments. A list uses position numbers in braces with a hash, like #{0} for the first item and #{1} for the second.

A record uses field names in square brackets with a hash, like #[city]. Pick the style that matches your data, and the values drop into the template in place of each token.

Example 1: Indexed placeholders with a list

Fill a sentence from a list, where #{0} takes the first item and #{1} takes the second.

let
    Source = Text.Format("Order #{0} ships to #{1}.", {1043, "Boston"})
in
    Source

Result: Order 1043 ships to Boston.

The number 1043 lands in #{0} and the text Boston lands in #{1}. If you need the number zero-padded to a fixed width first, format it with Text.PadStart before passing it in.

Example 2: Named placeholders with a record

When your values have names, pass a record and reference each field with #[fieldName].

let
    Source = Text.Format("#[product] costs #[price] each.", [product = "Keyboard", price = 25])
in
    Source

Result: Keyboard costs 25 each.

The product field fills #[product] and the price field fills #[price].

Example 3: Reusing the same index twice

You can use a placeholder more than once. Here #{0} appears twice and pulls the same list item both times.

let
    Source = Text.Format("#{0} plus #{0} equals #{1}.", {21, 42})
in
    Source

Result: 21 plus 21 equals 42.

Both #{0} tokens show 21, the first list item, and #{1} shows 42, the second.

Example 4: Formatting a date and duration with a culture

Pass "en-US" as the third argument to format the date and duration the United States way.

let
    Source = Text.Format("The #[distance] km run held in #[city] on #[date] took #[duration].",[city="Seattle",date=#date(2015,3,10),duration=#duration(0,0,54,40),distance=10],"en-US")
in
    Source

Result: The 10 km run held in Seattle on 3/10/2015 took 00:54:40.

The culture argument decides how the #date and #duration values turn into text. With "en-US", the date reads 3/10/2015 (month first).

Example 5: Same date formatted with a different culture

This is the same date as Example 4, but with "de-DE" instead.

let
    Source = Text.Format("Event date: #[date].",[date=#date(2015,3,10)],"de-DE")
in
    Source

Result: Event date: 10.03.2015.

Now the date reads 10.03.2015 (day first, dot separators) because the German culture formats dates differently. Same value, different output, driven entirely by culture.

Things to keep in mind with Text.Format

The # prefix is required. Writing {0} or [city] without the leading # leaves it as literal text in the output. This is the most common mistake.
An out-of-range index or missing field throws an error. Referencing #{2} when the list has two items, or #[total] when the record has no total field, raises Expression.Error.
culture defaults to the current environment. Without it, date and number output depends on the machine’s locale, so two people can get different results (compare Example 4 and Example 5). Pass a culture when the format must be predictable.
Values convert to text automatically. Numbers, dates, and durations are turned into text for you, so you do not need to wrap them in Text.From first. For tighter control over how a number or date reads, format it ahead of time with Number.ToText or Date.ToText and pass the result in.
It never folds. This is an in-memory string build that runs locally, so it does not push down to a database source.

Common questions about Text.Format

What is the difference between Text.Format, Text.Combine, and joining with &?

Text.Format fills a fixed template with placeholders, which keeps the wording readable when you have several values. Text.Combine joins a list of text values with an optional separator. The & operator concatenates two text values at a time, which gets unwieldy once the sentence has more than a couple of inserts.

List of All Power Query Functions

Related Power Query Functions / Articles: