Downloads
Power Query MNumberIntermediate

Number.FromText

Reads a number from text, using the separators of the culture you name.

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

Returns: The number, or null when the input is null. An error when the text is not a valid number for that culture.

What it does

Number.FromText reads an amount typed as text. The meaning of "1,234" depends on the culture. In the US it is one thousand two hundred and thirty four. In much of Europe the same characters may be read as one point two three four, or refused, because the comma is the decimal mark. Passing the culture makes the reading explicit, so a German bank file and a US bank file are both read correctly.

Syntax and arguments

Example

A bank in Germany sends amounts with a decimal comma and a thousands point, and a US bank sends them with a decimal point and a thousands comma. The team reads each feed with its own culture so the two sources give the same number for the same amount.

let
    UsFeed = Number.FromText("1,234.50", "en-US"),
    DeFeed = Number.FromText("1.234,50", "de-DE")
in
    {UsFeed, DeFeed}

Both values come out as 1234.5. The text was different in each feed, but the number is the same once the culture is named.

Example: a second, different use

Read an amount with no thousands separator, such as "980,25", as a German amount, so the decimal comma is taken as the decimal mark.

Things to watch

In business terms

Amounts from banks, payment processors and foreign subsidiaries arrive with different separators. Reading each feed with the right culture is the difference between a consolidation that ties out and one that is off by a factor of a thousand.

Related functions

All Power Query M functions · View as Markdown · M basics