Number.FromText Function in Power Query M (6 Easy Examples)

Number.FromText converts a number stored as text, like "250" or "12,847.5", into an actual number value. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If your imported data has amounts sitting as text and your calculations refuse to work, this is the function that fixes it.

Table of Contents

Syntax of Number.FromText Function

Number.FromText(text as nullable text, optional culture as nullable text) as nullable number

where

text (required, nullable text). The textual representation of a number, in a common number format such as plain digits, decimals, thousands separators, or scientific notation.
culture (optional, nullable text). Controls how text is interpreted, for example "en-US" or "de-DE". When omitted, your current system culture is used.

Returns: a nullable number value parsed from the text. If text is null, it returns null.

In plain terms, you hand it a number trapped inside text and it gives you back the real number, ready for math.

Example 1: Convert a number stored as text

Convert the text "250" into a number.

let
    Result = Number.FromText("250")
in
    Result

Result: 250

The text is parsed and comes back as a number value you can calculate with.

Example 2: Parse text with a thousands separator

Formatted numbers work too. You don’t need to clean out the comma first.

let
    Result = Number.FromText("12,847.5")
in
    Result

Result: 12847.5

The comma is read as a thousands separator and dropped, and the decimal point is kept.

Example 3: Use the culture argument for European formats

In German formatting, the dot is the thousands separator and the comma is the decimal mark. Tell Number.FromText that with the culture argument.

let
    Result = Number.FromText("1.499,90","de-DE")
in
    Result

Result: 1499.9

The "de-DE" culture tells Power Query to read the text the German way. Without it, a machine on US settings would misread the separators.

Example 4: Convert a text column to numbers

Say you have a Shipments query where the weights arrived as text like "4.8 kg". You want a numeric weight column you can actually use.

Here is the starting data:

Package	Weight
PKG-A101	4.8 kg
PKG-B204	12.5 kg
PKG-C317	0.75 kg
PKG-D425	23.4 kg

Add a column that strips the unit with Text.BeforeDelimiter, then converts what is left:

let
    Source = Excel.CurrentWorkbook(){[Name="Shipments"]}[Content],
    AddedWeight = Table.AddColumn(Source,"WeightKg",each Number.FromText(Text.BeforeDelimiter([Weight]," ")),type number)
in
    AddedWeight

For each row, the text before the space ("4.8") is converted to a number in the new WeightKg column.

The result has the numeric column added:

Package	Weight	WeightKg
PKG-A101	4.8 kg	4.8
PKG-B204	12.5 kg	12.5
PKG-C317	0.75 kg	0.75
PKG-D425	23.4 kg	23.4

The type number argument on Table.AddColumn also types the new column properly, so no separate change-type step is needed.

Example 5: Sum amounts stored as text

Converted values are real numbers, so you can feed them straight into math. Here, expense amounts arrived as text like "USD 84.50".

Here is the starting data:

Item	Amount
Conference travel	USD 84.50
Venue deposit	USD 219.75
Print materials	USD 45.25

Pull the part after the space, convert each value, and total them:

let
    Source = Excel.CurrentWorkbook(){[Name="Expenses"]}[Content],
    Amounts = List.Transform(Source[Amount],each Number.FromText(Text.AfterDelimiter(_," "))),
    Total = List.Sum(Amounts)
in
    Total

Result: 349.5

Text.AfterDelimiter strips the "USD " prefix, and List.Transform runs the conversion over every value. List.Sum then adds them up.

Example 6: Handle non-numeric text with try otherwise

If a value like "Pending" slips into the column, Number.FromText throws an error. Wrap the call in try ... otherwise to return a fallback instead.

let
    Result = try Number.FromText("Pending") otherwise 0
in
    Result

Result: 0

The conversion fails, so the otherwise branch kicks in and returns 0. Use this pattern whenever a text column might hold stray non-numeric values.

Things to keep in mind with Number.FromText

Non-numeric text throws an error, it does not return null. The exact message is Expression.Error: We couldn't convert to Number. Guard with try ... otherwise (Example 6) when the data is messy.
Currency symbols are not accepted. Number.FromText("$84.50") errors. Strip the symbol first, or use Currency.From instead. Leading and trailing spaces are fine, though.
The default culture is the machine’s system locale. The same text can parse to a different number on someone else’s computer. Pass the culture argument explicitly when a query has to run elsewhere.
Scientific notation is a valid input. Text like "5.0E-10" converts without any special handling.

Common questions about Number.FromText

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

Number.From accepts many types, including logicals, dates, and datetimes. Number.FromText only accepts text, which makes it stricter and self-documenting when text is all you expect.

For the reverse trip, number to formatted text, use Number.ToText.

Should I use Number.FromText or just change the column type?

For a whole column, changing the type with Table.TransformColumnTypes (or Change Type, Using Locale in the UI) does the same conversion in one step. Reach for Number.FromText when you need row-level control, like cleaning the text first or guarding errors.

List of All Power Query Functions

Related Power Query Functions / Articles: