Value.Type

Basic Syntax

The basic syntax of the Value.Type function is as follows:

Here, the `expression` parameter is the value for which we want to determine the data type, and the `type` parameter is the name of the data type that the expression represents.

For example, to determine the data type of a column named “StartDate” in a table named “SalesData”, we would use the following M code:

This would return the name of the data type of the values in the “StartDate” column, such as “date” or “text”.

Supported Data Types

The Value.Type function supports a wide range of data types, including:

– `logical`: Boolean true/false values.

– `number`: Numeric values, including integers, decimals, and fractions.

– `date`: Dates and times.

– `duration`: Time durations.

– `text`: Text strings.

– `binary`: Binary data.

In addition, the Value.Type function can also return the following special data types:

– `list`: A list of values.

– `record`: A collection of named values.

– `table`: A table of data.

Automatic Type Detection

In many cases, the Power Query engine is able to automatically detect the data type of a given expression, without the need for the Value.Type function. For example, if we import a CSV file into Power Query, the engine will automatically detect the data type of each column based on the values in the first few rows of the file.

However, there may be cases where the automatic type detection is incorrect, or where we need to manually specify the data type of an expression. In such cases, the Value.Type function can be a useful tool.

Example: Converting Text to Dates

One common use case for the Value.Type function is converting text strings to dates. For example, suppose we have a column named “Date” in a table named “SalesData”, where the dates are stored as text strings in the format “yyyy-mm-dd”. We can use the Value.Type function to convert these strings to dates, as follows:

Here, we use the `Table.TransformColumns` function to transform the “Date” column. The `each` keyword is used to specify that we want to apply the transformation to each value in the column. The `Date.FromText` function is used to convert the text string to a date value, using the “yyyy-mm-dd” format.

Before applying this transformation, we can use the Value.Type function to verify that the “Date” column is currently of the “text” data type:

After applying the transformation, we can use the Value.Type function again to verify that the “Date” column is now of the “date” data type:

In conclusion, the Value.Type function is a powerful tool for determining the data type of a given expression in Power Query. By understanding the M code behind this function and its various parameters, we can perform complex data transformations and manipulations with ease.

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

Upcoming Courses

Contact Us