Text.InferNumberType

Updated on

Text.InferNumberType is a Power Query M function that infers the granular number type of a text value. The function returns the inferred number type, with an optional culture parameter to customize the inference.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Text.InferNumberType(
   text as text,
   optional culture as nullable text,
) as type
ArgumentAttributeDescription
textThe text value that is evaluated.
cultureoptionalThe 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

In essence, this function looks at your text string, evaluates it, and tells you if it’s an Int64.Type, Double.Type, or any other type of number. Just remember that if your text recognize your text as a number, this function will show an error. You can provide an optional culture for to respect cultures when deducing the number type (for example, “en-US”).

Examples

Let’s look at some examples to get a better understanding of the function Text.InferNumberType.

The main purpose of this function is to deduce the number type from a string. Imagine having the following string “€11.11”. Power Query can make a guess which number type this is by using the following code:

Text.InferNumberType( "€11.11" ) // Output: currency type

It tells you it’s a currency type.

But that’s not all. There are different types of numbers that Text.InferNumberType can help you infer:

Text.InferNumberType( "9,5" )   // Output: wholenumber
Text.InferNumberType( "9.5" )   // Output: number
Text.InferNumberType( "12.3%" ) // Output: percentage

One of options you can use in Text.InferNumberType is letting it respect a cultural context. For example, the “9,5” value is considered a wholenumber in the “en-US” culture, but in the Dutch language, the comma is used as a decimal separator. So, if you specify “nl-NL” as the culture, you get a different output:

Text.InferNumberType( "9,5", "nl-NL" )   // Output: number
Text.InferNumberType( "9.5", "nl-NL" )   // Output: wholenumber

That means you can use the Text.InferNumberType function to help you understand the number types hidden in your text strings.

Learn more about Text.InferNumberType in the following articles:

Other functions related to Text.InferNumberType are:

BI Gorilla Youtube Channel

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