Number.From
Converts a value of another type, such as text or a logical, to a number.
Number.From(value as any, optional culture as nullable text) as nullable numberReturns: The number, or null when the input is null. An error when the value cannot be converted.
What it does
Number.From turns a value into a number when Power Query finds a sensible conversion. Text that looks like a number becomes a number, and true and false become 1 and 0. It is the general conversion. When a column is text that contains a culture specific number, such as a European amount with a decimal comma, use Number.FromText with an explicit culture instead.
Syntax and arguments
- value: the value to convert.
- culture (optional): the culture to read text with, such as "en-US". Leave it out to use the machine's setting.
Example
A flag column in an export marks each line that has been reconciled with 1 or 0 as text, and the amount column has been loaded as text too. The team converts both to numbers so the reconciled total can be summed.
let
Lines = #table(type table [Amount = text, Reconciled = logical],
{{"120.50", true}, {"75.00", false}, {"300", true}}),
Typed = Table.TransformColumns(Lines, {
{"Amount", each Number.From(_, "en-US"), type number},
{"Reconciled", each Number.From(_), type number}
}),
Total = List.Sum(Table.SelectRows(Typed, each [Reconciled] = 1)[Amount])
in
Total
The reconciled rows are 120.5 and 300, so Total is 420.5. Logical values become 1 for true and 0 for false, which is how the reconciled flag is summed.
Example: a second, different use
Convert a text count such as "42" from a system export to a number before a Table.Group step that sums it.
Things to watch
- Number.From on text uses the culture of the machine unless you pass one. A decimal comma text then fails or reads wrong, so pass the culture when the source is not in the same country as the machine.
- Text with a thousands separator or a currency sign may not convert. Clean it with Text.Replace or Text.Remove first.
- Null converts to null, and a value that cannot be converted gives an error. Use try ... otherwise for the bad rows.
- For a whole column, Table.TransformColumnTypes with type number does the same job.
In business terms
Columns that arrive as text, or flags that arrive as true and false, must become numbers before they can be totaled. A clean numeric type on each column is what makes a totals row agree with the source.