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| Argument | Attribute | Description |
|---|---|---|
| Number | The number value to transform into text. | |
| Format | Optional | The 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. |
| Culture | Optional | The 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+000Notice 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.
| Category | Format | 12,345.6789 | 12,345 | -1.15 |
|---|---|---|---|---|
| Currency | C, c | $12,345.68 | $12,345.00 | -$1.15 |
| Decimal | D, d | 12345 | ||
| Exponential | E, e | 1,23E+10 | 1,23E+10 | -1.150000E+000 |
| Fixed-Point | F, f | 12345.68 | 12345.00 | -1.15 |
| General | G, g | 123.456.789 | 12345 | -1.15 |
| Number | N, n | 12,345.68 | 12,345.00 | -1.15 |
| Percent | P, p | 1,234,567.89% | 1,234,500.00% | -115.00% |
| Round-trip | R, r | 123.456.789 | 12345 | -1.15 |
| Hexadecimal | X, 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:
| Category | Format | 12,345.6789 | 12,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"
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" )
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.
Related articles
Learn more about Number.ToText in the following articles:
- 3 Ways to Add Leading Zeros to Numbers in Power Query
Leading zeros help when you need a fixed value length. Yet, they can also help when sorting text values that include numbers. This post … » Read more - Mastering Custom Format Strings in Power Query
This guide shows how to use custom format strings to format values in the M language. » Read more
Related functions
Other functions related to Number.ToText are:
- Byte.From
- Currency.From
- Decimal.From
- Double.From
- Int16.From
- Int32.From
- Int64.From
- Int8.From
- Number.From
- Number.FromText
- Percentage.From
- Single.From
2023-2026 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy