If you want to turn a number into text in Power Query, with control over decimals, percent signs, or leading zeros, Number.ToText is the function for the job.
It converts a number to its text form, and an optional format code lets you decide exactly how that text looks.
Syntax of Number.ToText Function
Number.ToText(number as nullable number, optional format as nullable text, optional culture as nullable text) as nullable text
where
number(required, nullable number). The number you want to convert to text.format(optional, nullable text). A .NET numeric format string such as"N2","P1", or"D6". Omit it for a plain conversion with no formatting.culture(optional, nullable text). A culture name such as"fr-FR". It controls separators and symbols, like which character is used for the decimal mark.
Returns: the number as a text value, formatted according to the optional format string and culture.
In plain terms, you hand it a number and it gives you back text, shaped however the format code and culture tell it to.
Example 1: Convert a number to plain text
Turn a whole number into text with no special formatting.
Number.ToText(8675)
Result: 8675
With no format code, you get a straight conversion. The digits are now a text value instead of a number.
Example 2: Show two decimals with a thousands separator
Use the "N2" format to fix the output at two decimal places and add a thousands separator.
Number.ToText(2048.5,"N2")
Result: 2,048.50
The N code is the standard number format, and the 2 sets two decimal places. It also adds a comma for the thousands grouping.
Example 3: Format a number as a percent
Use the "P1" format to show a decimal as a percentage with one decimal place.
Number.ToText(0.082,"P1")
Result: 8.2%
The P code multiplies the value by 100 and appends the percent sign, so 0.082 becomes 8.2%.
Example 4: Zero-pad an integer to a fixed width
Use the "D6" format to pad an ID with leading zeros until it is six digits wide.
Number.ToText(42,"D6")
Result: 000042
The D code is for integers, and the 6 is the minimum number of digits. Short numbers get padded with leading zeros to reach that width. This is handy when you later concatenate the padded value into a code or ID.
Example 5: Format a number in another culture
Pass "fr-FR" as the culture so the output uses French conventions.
Number.ToText(3.14159,"N2","fr-FR")
Result: 3,14
The "N2" format rounds to two decimals, and the French culture uses a comma as the decimal mark instead of a period.
Things to keep in mind with Number.ToText
- The format codes are standard .NET numeric format strings. The common ones are
N,F,P,C,D,X, andE, plus custom patterns like0000. Look up the standard .NET numeric format codes for the full set. Donly works on integers. Using aDformat on a number with decimals throws an error. For padding values that may have decimals, use a custom pattern like"0000.00"instead.- Output is culture-dependent unless you pass
culture. Separators, the decimal mark, and percent spacing can change with the machine’s regional settings. That is what thecultureargument is for. Pass something like"en-US"and you get the same result on every machine. - To go the other way, use
Number.FromText. That parses a text value back into a number. - The result is text, so text functions work on it. Once converted you can run something like Text.Contains on the output, for example to check for the percent sign.
Common questions about Number.ToText
What is the difference between Number.ToText and Text.From?
Text.From is a general type-to-text converter with no formatting control, so it just gives you the plain text form of any value. Number.ToText adds the format and culture arguments, so you can decide on decimals, percent, padding, and regional separators.
How do I add leading zeros to a number?
Use a "D6" format for six digits, or a custom pattern like "000000", as shown in Example 4. Both pad shorter numbers with leading zeros up to the width you specify.
List of All Power Query Functions
Related Power Query Functions / Articles:
