Number.From Function in Power Query M (5 Easy Examples)

If you want to turn a text value, a logical, or a date into an actual number in Power Query, the Number.From function is what you reach for. In this article I’ll show you how it works with a few simple examples.

Table of Contents

Syntax of Number.From Function

Number.From(value as any, optional culture as nullable text) as nullable number

where

value (required, any). The value you want to convert to a number. It can be text, logical, date, datetime, time, duration, or a number already.
culture (optional, nullable text). A culture code such as "en-US" or "fr-FR" that tells the function how to read a text value. Omit it to use the default culture.

Returns: a number value built from value. If value is null, it returns null.

In plain terms, you hand it a value of almost any type and it gives you back the number behind it.

Example 1: Convert a text value to a number

Turn the text "42" into a real number you can do math with.

let
    Source = Number.From("42")
in
    Source

Result: 42

The text "42" is read as the number 42.

Example 2: Convert text with a decimal point

Number.From handles decimals in text too, not just whole numbers.

let
    Source = Number.From("3.14")
in
    Source

Result: 3.14

The decimal point is kept, so you get 3.14 back as a number.

Example 3: Convert a logical value to a number

A true or false value becomes 1 or 0.

let
    Source = Number.From(true)
in
    Source

Result: 1

true converts to 1, and false would convert to 0.

Example 4: Convert a date to its serial number

Behind every date is a serial number, and this is what you get back.

let
    Source = Number.From(#date(2020,1,1))
in
    Source

Result: 43831

January 1, 2020 is day 43831 counting from the date system’s start, so that is the value you get.

Example 5: Read text in a different culture

When your text uses a comma as the decimal separator, pass a culture so it reads correctly.

let
    Source = Number.From("3,14","fr-FR")
in
    Source

Result: 3.14

With "fr-FR", the comma is treated as the decimal point, so "3,14" becomes 3.14. If you are parsing strict text instead, Date.FromText follows the same culture idea for dates.

Things to keep in mind with Number.From

An unsupported type throws an error. Passing something like a list or record returns Expression.Error: We cannot convert a value of type List to type Number. Convert to a supported type first.
Bad text also throws. Number.From("abc") returns DataFormat.Error: We couldn't convert to Number. Only text that actually looks like a number converts.
Culture changes how text is read, not the result type. The output is always a plain number. culture only affects how separators in value are parsed.
Time and duration convert to fractional days. Number.From on a time or duration gives you a fraction of a day, not seconds, so 12:00 becomes 0.5.

Common questions about Number.From

What is the difference between Number.From and Number.FromText?

Number.From accepts any type and figures out the conversion. Number.FromText only takes text and is stricter about the format it expects. To go the other way and turn a number into text, use Text.From or Number.ToText.

Does Number.From round decimals to whole numbers?

No, it keeps the full value. Use Number.Round or Int64.From if you need a whole number afterward.

List of All Power Query Functions

Related Power Query Functions / Articles: