Number.ToText

Updated on

Number.ToText is a Power Query M function that formats a numeric value to a text value according to a specified format and an optional culture. The function returns the formatted text value based on the format code and precision specifier provided.

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

Syntax

Number.ToText(
   number as nullable number,
   optional format as nullable text,
   optional culture as nullable text,
) as nullable text
ArgumentAttributeDescription
NumberThe number value to transform into text.
FormatOptionalThe Format field is used to specify the output format of the transformation. You can add a single alphabetic character to specify a standard format, and a Precision specifier to affect the number of digits in the resulting string. For example, the text “D3” returns the decimal representation with a precision of 3 decimals. Alternatively, you can also provide Custom Numeric Format Strings.
CultureOptionalThe Culture field is used to ensure the transformation supports local formats. This can influence elements like currency symbols and decimal separators. For instance, you can enter “nl-NL or “en-US”. If you omit this argument, the default format used will align with the culture of your system, using Culture.Current.

Description

The Number.ToText function converts numbers into their textual representation. It offers two optional parameters: the format and the culture parameter.

  • Format: lets you determine the output’s appearance, Both standard custom numeric format strings are supported. Users can dictate the format, and adjust the precision to depict their desired number of decimals.
  • Culture: The culture parameter plays a role in determining specific elements, like the currency sign and the decimal point. As an example, the U.S. and the Netherlands have distinct decimal separators. This flexibility guarantees data is returned clearly and precisely.

Examples

Let’s look at some examples of how the Number.ToText function can be used:

Using Number.ToText for Simple Conversions

To format a number as text without a specified format:

Number.ToText( 4 ) // Output: "4"

Applying Standard Numeric Format Strings

The Number.ToText function supports a range of custom format strings that change the output format of your values.

For instance, to format a number as text in Exponential format:

Number.ToText( 4, "e" ) // Output: 4.000000e+000

Notice that it uses the lower letter letter ‘e’ as input, but you may as well have used the capital ‘E’. The outcome is the same.

To format a number as text in Decimal format with a precision of a single decimal, you can write:

Number.ToText( -0.1234, "P1" ) // Output: -12.3%

Standard Numeric Format Strings

When you’re working with the Number.ToText function and want to control how the output looks, Standard Numeric Format Strings work wonders, but you need to know which are available. Let’s dive into the standard numeric format strings you can use for numbers.

CategoryFormat12,345.678912,345-1.15
CurrencyC, c$12,345.68$12,345.00-$1.15
DecimalD, d 12345
ExponentialE, e1,23E+101,23E+10-1.150000E+000
Fixed-PointF, f12345.6812345.00-1.15
GeneralG, g123.456.78912345-1.15
NumberN, n12,345.6812,345.00-1.15
PercentP, p1,234,567.89%1,234,500.00%-115.00%
Round-tripR, r123.456.78912345-1.15
HexadecimalX, x 3039

Regardless of whether you use uppercase or lowercase characters, the format remains consistent. Now, here’s what each code stands for:

  • Currency (C/c): Formats numbers as monetary values.
  • Decimal (D/d): Shows numbers as whole digits. You can control the number of digits shown.
  • Exponential (E/e): Represents numbers in exponential form. It shows up to 6 decimal digits by default, but you can adjust this using the precision specifier.
  • Fixed-Point (F/f): This includes integral and decimal digits.
  • General (G/g): This is the most compact form of either fixed-point or scientific.
  • Number (N/n): Shows numbers with group and decimal separators.
  • Percent (P/p): This multiplies the number by 100 and displays it with a percent symbol.
  • Round-trip (R/r): This is a text value that can round-trip an identical number. The precision specifier is ignored.
  • Hexadecimal (X/x): Transforms numbers into their hexadecimal equivalent.

Providing a Precision Specifier

Additionally, you can specify the precision of your values. That allows you to indicate how many decimals you desire as output. To do that, provide any of the above characters as a format string and add a suffix with a number of decimals between 0 and 9.

For instance, the following two versions specify a different number of decimals for the output.

// Output: $12,345.68
Number.ToText( 12345.6789, "C2" )

// Output: $12,345.678900000
Number.ToText( 12345.6789, "C9" )

Applying this to the Currency format would then look as follows:

CategoryFormat12,345.678912,345-1.15
Currency (default)C$12,345.68$12,345.00-$1.15
Currency (0 decimals)C0$12,346$12,345-$1
Currency (1 decimals)C1$12,345.7$12,345.0-$1.2
Currency (2 decimals)C2$12,345.68$12,345.00-$1.15
Currency (3 decimals)C3$12,345.679$12,345.000-$1.150
Currency (4 decimals)C4$12,345.6789$12,345.0000-$1.1500
Currency (5 decimals)C5$12,345.67890$12,345.00000-$1.15000
Currency (6 decimals)C6$12,345.678900$12,345.000000-$1.150000
Currency (7 decimals)C7$12,345.6789000$12,345.0000000-$1.1500000
Currency (8 decimals)C8$12,345.67890000$12,345.00000000-$1.15000000
Currency (9 decimals)C9$12,345.678900000$12,345.000000000-$1.150000000

The example works well for the Currency example, but you can apply it to any Custom Format Strings.

Custom Numeric Format Strings

With the Number.ToText function, you’re not limited to standard formatting. You can delve into Custom Numeric Formats, giving you even more flexibility. Take a look at how you can convert the number values in the Column headers into text using the string in the Format column.

Number.ToText( -100, "0000" ) // Output: "-0100"
Number.ToText( -100, "0.00" ) // Output: "-100.00"
Number.ToText with Custom Numeric Format Strings

However, remember that the results you get might be influenced by the locale settings on your system. To illustrate, the US and the Netherlands differ in terms of decimal separators and default currency symbols:

Number.ToText( 0.114455, "G", "nl-NL" ) // Output: "0,114455"
Number.ToText( 0.114455, "G", "en-US" ) // Output: "0.114455"

Number.ToText( 0.114455, "C", "nl-NL" ) // Output: "€0,11"
Number.ToText( 0.114455, "C", "en-US" ) // Output: "$0.11"

The distinct outputs for each locale can be observed through these formulas:

Number.ToText( [Value], [Format], "nl-NL" )
Number.ToText( [Value], [Format], "en-US" )
Number.ToText with different culture codes in Power Query M

In conclusion, the Number.ToText function is a powerful function that allows you to format numeric values into text, providing flexibility and precision when outputting your data.

Learn more about Number.ToText in the following articles:

Other functions related to Number.ToText are:

BI Gorilla Blog

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