Number.From - Function | Power Query How

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 number

Argument	Attribute	Description
value		The value to extract a number from.
culture	optional	The culture argument enables the specification of a Culture code (e.g., “nl-NL” or “en-US”) to align transformations with local formatting conventions. If this argument is omitted, functions default to Culture.Current, which reflects the system’s regional settings.

Description

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: A number value from textual representation. Common text formats are handled (“15”, “3,423.10”, “5.0E-10”). Refer to Number.FromText for details.
logical: 1 for true, 0 for false.
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 of value.
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: 100

In 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

Number.From function turns values into numbers in Power Query M

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" )

Number.From respect culture to turn values into numbers in Power Query M

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.

Other functions related to Number.From are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/number-from

Syntax

Description

Examples

Related functions