Text.InferNumberType

In this article, we will take a closer look at the M code behind the Text.InferNumberType function, and see how it can be used to improve data analysis.

What is the Text.InferNumberType function?

The Text.InferNumberType function is used to automatically detect the data type of a column in a table. This function is particularly useful when dealing with large datasets, as it can save a considerable amount of time that would otherwise be spent manually checking the data type of each column.

The Text.InferNumberType function works by analyzing the values in a column and determining their data type based on certain criteria. For example, if a column contains only integers, the Text.InferNumberType function will recognize it as an integer column.

How does the Text.InferNumberType function work?

The Text.InferNumberType function works by analyzing the values in a column and determining their data type based on certain criteria. The function considers the following criteria when determining the data type of a column:

– The presence of decimal points

– The presence of negative signs

– The presence of scientific notation

– The number of characters in a value

Based on these criteria, the Text.InferNumberType function can recognize the following data types:

– Integer

– Decimal

– Scientific

– Text

Let’s take a closer look at how the Text.InferNumberType function analyzes each of these data types.

Integer

An integer is a whole number without a decimal point. To be recognized as an integer column, the Text.InferNumberType function looks for a column where all of the values meet the following criteria:

– The value can be converted to a whole number without rounding

– The value does not contain any decimal points or scientific notation

– The value does not contain any negative signs

If all of the values in a column meet these criteria, the Text.InferNumberType function will recognize the column as an integer column.

Decimal

A decimal is a number with a decimal point. To be recognized as a decimal column, the Text.InferNumberType function looks for a column where all of the values meet the following criteria:

– The value can be converted to a decimal number without rounding

– The value contains a decimal point

– The value does not contain any scientific notation or negative signs

If all of the values in a column meet these criteria, the Text.InferNumberType function will recognize the column as a decimal column.

Scientific

Scientific notation is a way of expressing numbers that are very large or very small. It is written in the form of a number multiplied by a power of 10. To be recognized as a scientific column, the Text.InferNumberType function looks for a column where all of the values meet the following criteria:

– The value can be converted to a number in scientific notation

– The value contains a power of 10

– The value does not contain any decimal points or negative signs

If all of the values in a column meet these criteria, the Text.InferNumberType function will recognize the column as a scientific column.

Text

Any value that does not meet the criteria for an integer, decimal, or scientific column is considered a text value. To be recognized as a text column, the Text.InferNumberType function looks for a column where all of the values are text values.

The Text.InferNumberType function is a powerful tool in Power Query that allows users to automatically detect the data type of a column in a table. By analyzing the values in a column based on certain criteria, the Text.InferNumberType function can recognize integer, decimal, scientific, and text columns.

Using the Text.InferNumberType function can save a considerable amount of time that would otherwise be spent manually checking the data type of each column. This function is particularly useful when dealing with large datasets, where manually checking each column would be time-consuming and inefficient.

Overall, the Text.InferNumberType function is a valuable tool for any Power Query user looking to improve their data analysis skills.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us