Text.Format

Text.Format is a Power Query M function that formats a text value by applying arguments from a list or record to a format string. The function returns the formatted text, with an optional culture parameter to customize the formatting.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Argument	Attribute	Description
formatString		The base format string the function will format.
arguments		Contains the data to add to the format string. You can specify these either in the form of a list or a record.
culture	optional	The culture argument enables the specification of a Culture code (e.g., “nl-NL” or “en-US”) to align transformations with local formatting conventions. If this argument is omitted, functions default to Culture.Current, which reflects the system’s regional settings.

Description

Returns formatted text that is created by applying arguments from a list or record to a format string formatString. An optional culture may also be provided (for example, “en-US”).

Here, ‘formatString’ is the text that you want to manipulate, and ‘arguments’ refers to the data that you want to apply to your format string. Additionally, you have the option to specify a ‘culture’ parameter. This is useful if you want your formatted text to adhere to specific regional or cultural formatting norms (such as “en-US” for United States English).

Examples

To understand the Text.Format function, let’s explore some examples.

Providing Records

Imagine you’re crafting a story about a gorilla wanting a banana. Instead of hardcoding the values, you can use the Text.Format function to dynamically insert values into your text:

Text.Format( 
   "The #[Subject] wants a #[Food].", 
   [ Subject = "Gorilla", Food = "Banana" ] 
)

In this example, the function dynamically inserts the values for “Subject” and “Food” into the text. The placeholders (preceded by a hashtag #) are defined in the formatString argument, and their values are provided in a record as the second argument

Providing Lists

Let’s say you want to create a sentence stating that Rick, Ben, and Mila are friends. The Text.Format function also supports this with a list approach:

Text.Format( 
   "#{0}, #{1} and #{2} are friends.", 
   { "Rick", "Ben", "Mila" } 
)

This method uses a zero-based index for each placeholder, with the corresponding values provided in a list as the second argument. It references the index positions in the format string, making it efficient for lists.

Using the Culture Argument

What if you have monetary values and dates that need to be formatted according to specific cultural norms? The optional ‘culture’ parameter is useful here.

For example, creating a payment reminder for a US audience:

Text.Format( 
   "Please pay us €#{0} before #{1}.", 
   { 49.99, #date( 2022, 5, 1 ) }, 
   "EN-us" 
)

The function will return: “Please pay us €49.99 before 5/1/2022.” Notice a full stop as separator and the date in format of M/D/YYYY.

For a Dutch audience, the same reminder will look different due to local formatting conventions:

Text.Format( 
   "Please pay us €#{0} before #{1}.", 
   { 49.99, #date( 2022, 5, 1 ) }, 
   "NL-nl" 
)

In this case, the output is: “Please pay us €49,99 before 1-5-2022.” In this case there is a comma as separator and a date in format D-M-YYYY.

Related articles

Learn more about Text.Format in the following articles:

• Text Functions in Power Query M (150+ Examples)
  Your guide to Text Functions in Power Query M. Learn from practical examples and master Power Query’s most useful Text functions. » Read more

Related functions

Other functions related to Text.Format are:

• Text.Clean
• Text.PadEnd
• Text.PadStart
• Text.Trim
• Text.TrimEnd
• Text.TrimStart

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/text-format