Text.InferNumberType - Text Function

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

Argument	Attribute	Description
text		The text value that is evaluated.
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

The Text.InferNumberType 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.

Inferring Number Types from Strings

The main purpose of this function is to deduce the number type from a string. For example, if you have the string “€11.11”, Power Query can guess the number type using the following code:

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

It tells you it’s a currency type.

Different Types of Numbers

There are various types of numbers that Text.InferNumberType can help you identify:

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

Respecting Cultural Context

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

This means you can use the Text.InferNumberType function to help you understand the number types hidden in your text strings, considering different cultural contexts.

Learn more about Text.InferNumberType in the following articles:

Text Functions in Power Query M (150+ Examples)
Your guide to Text Functions in Power Query M. Learn from practical examples and master Power Query’s most useful Text functions. » Read more

Other functions related to Text.InferNumberType are:

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