Number.ToText
Writes a number as text, as it is or in a format such as thousands separators or a fixed number of decimals.
Group: Number. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Number.ToText(number as nullable number, optional format as nullable text, optional culture as nullable text) as nullable text| Parameter | Type | Description |
|---|---|---|
number | nullable number | The number to work on; null gives null. |
format | nullable text optional | A standard format ("D5", "F2", "N2", "E3") or a pattern such as "0.00" or "#,##0.00"; the shortest text that reads back as the same number when left out. |
culture | nullable text optional | A culture's name, such as "nb-NO". |
Returns
nullable text. The number as text; null for null.
Remarks
- "D" pads a whole number with zeros to a length (42 with "D5" is 00042), "F" fixes the decimals, "N" fixes them and groups thousands, "P" writes a per cent, "E" an exponent of at least three digits (1.23E+003). The digit after the letter is the count.
- In a pattern, each 0 after the point is a decimal that is always written; a comma in the pattern groups thousands, and a % multiplies by 100.
- A format rounds a half away from zero: 0.125 with "F2" is 0.13.
- Separators, the decimal sign and the currency are the culture's: name one, or the query's culture is used (en-US in these examples).
Compared with Power Query
Known difference in OpenBI Studio 0.3.79, found while this page was written and run on its engine:
Number.ToText(1e15 * 1, "N0"): Power Query gives "1000000000000000": a computed number from 1E+15 up is written as its plain digits whatever the format, while a number written in the query or read from text follows the format. OpenBI follows the format for every number: "1,000,000,000,000,000".
Examples
An amount with thousands separators
Number.ToText(1234.5, "N2")"1,234.50"The same amount in Norwegian
Number.ToText(1234.5, "N2", "nb-NO")"1 234,50"A space groups the thousands (a no-break one) and a comma marks the decimals.
A pattern
Number.ToText(1234.5678, "#,##0.00")"1,234.57"