Text.Format

Updated on

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
ArgumentAttributeDescription
formatStringThe base format string the function will format.
argumentsContains the data to add to the format string. You can specify these either in the form of a list or a record.
cultureoptionalThe 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 a situation where you’re crafting a narrative about a gorilla desiring 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" ] 
)
Text.Format function using Records as input in Power Query M

The above example provides a dynamic way to input your values. It achieves this by providing the formatString with temporary column names (preceded by a hashtag #). You then define the values of these temporary column names by providing its details in a record in the second argument.

Providing Lists: Or let’s say you want to create a sentence stating that Rick, Ben, and Mila are friends. The Text.Format function supports an alternative way for providing this.

Text.Format( 
   "#{0}, #{1} and #{2} are friends.", 
   { "Rick", "Ben", "Mila" } 
)
Text.Format function using Lists as input in Power Query M

This approach uses a zero-based index for each placeholder, with the corresponding values provided in the second argument, making it an efficient method when dealing with lists.

This approach uses alternative way to provide the values. It references the zero-based index position of each variable in the formatString (preced by a hashtag #). The list with items is then provided in the second argument.

Culture Argument: What if you have monetary values and dates that need to be formatted according to a specific cultural norm? That’s where the optional ‘culture’ parameter can be especially useful.

Here’s how you could create a payment reminder for a US audience:

Text.Format( 
   "Please pay us €#{0} before #{1}.", 
   { 49.99, #date( 2022, 5, 1 ) }, 
   "EN-us" 
)
Text.Format function using a en US culture code in Power Query M

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 a little different due to local formatting conventions:

Text.Format( 
   "Please pay us €#{0} before #{1}.", 
   { 49.99, #date( 2022, 5, 1 ) }, 
   "NL-nl" 
)
Text.Format function using a NL nl culture code in Power Query M

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.

To conclude, Text.Format easily tailors text outputs to match specific formats and cultural norms, adding an extra layer of customization to your data presentation in Power Query M.

Learn more about Text.Format in the following articles:

Other functions related to Text.Format are:

BI Gorilla Blog

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