Number.From is a Power Query M function that converts a given value to a number value, supporting various input types and an optional culture parameter. The function returns the converted number value or null if the input is null, and an error if the input value cannot be converted.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Syntax
Number.From(
value as any,
optional culture as nullable text,
) as nullable numberDescription
Returns a number value from the given value. An optional culture may also be provided (for example, “en-US”). If the given value is null, Number.From returns null. If the given value is number, value is returned. Values of the following types can be converted to a number value:
text: Anumbervalue from textual representation. Common text formats are handled (“15”, “3,423.10”, “5.0E-10”). Refer to Number.FromText for details.logical: 1 fortrue, 0 forfalse.datetime: A double-precision floating-point number that contains an OLE Automation date equivalent.datetimezone: A double-precision floating-point number that contains an OLE Automation date equivalent of the local date and time ofvalue.date: A double-precision floating-point number that contains an OLE Automation date equivalent.time: Expressed in fractional days.duration: Expressed in whole and fractional days.
If value is of any other type, an error is returned.
Examples
The Number.From function allows us to convert values into their numerical equivalents. Let’s enrich our understanding of this function by diving into some examples.
Imagine you have the string “100” and need to perform numerical operations on it. The Number.From function transforms this string into a number. Have a look at this example:
Number.From( "100" ) // Output: 100In this example, our string “100” is converted into its numerical counterpart 100.
Number.From function is useful because of its flexibility. It can interact with a variety of value types. Let’s see some examples to illustrate this:
Number.From( "9,5") // Output: 95
Number.From( "9.5") // Output: 9.5
Number.From( "12,3%") // Output: 0.123
Number.From( "12.3%") // Output: 1.23
Number.From( true ) ) // Output: 1
Number.From( #binary( "AQID" ) ) // Output: error
Number.From( #duration( 0, 1, 30, 0 ) ) // Output: 0.0625
Number.From( #time( 23, 30, 05 ) ) // Output: 0.979224537
Number.From( #date( 2023, 12, 25 ) ) // Output: 45285
Number.From( #datetime( 2013, 02, 26, 09, 15, 00 ) ) // Output: 41331.38542
Number.From( #datetimezone(2013, 02, 26, 09, 15,00, 09, 00 ) ) // Output: 41331.01042
One remarkable thing to note is that this function respects cultural variations in number formatting. For example, the United States and the Netherlands have different conventions for presenting numbers. In Power Query, you can indicate the desired culture using an optional argument, as shown below:
Number From en-US = Number.From( [Values], "en-US" )
Number From nl-NL = Number.From( [Values], "nl-NL" )
It’s important to note that the Number.From function can’t handle every value. In the above example we already saw an error for converting a binary value to a number. Additionally, trying to convert complex structures such as lists, records, tables, functions, and types into a number leads to a data format error.
/* All below statements lead to
DataFormat.Error: We couldn't convert to Number. */
Number.From( { 1, 2, 3 } )
Number.From( [ A = 1, B = 2 ] )
Number.From( #table( { "X", "Y" }, { { 0,1 }, { 1,0 } } ) )
Number.From( (x) => x + 1 )
Number.From( type date )By understanding how to use the Number.From function, you can convert a variety of data types into their numerical equivalents with ease. Whether you are dealing with percentages, dates, times, or even true/false values, this function can do it all.
Related functions
Other functions related to Number.From are:
- Byte.From
- Currency.From
- Decimal.From
- Double.From
- Int16.From
- Int32.From
- Int64.From
- Int8.From
- Number.FromText
- Number.ToText
- Percentage.From
- Single.From
