Value.Traits

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Value.Traits

What is Value.Traits Function?

The Value.Traits function is a part of the Power Query M language and is used to extract information about a column or a value from a table. It returns a record that contains various properties of the value such as its data type, precision, scale, and others. The function takes a single argument, which can be any value or column in a table.

Syntax

The syntax for the Value.Traits function is as follows:

Value.Traits(value as any) as record

The argument `value` can be any value or column in a table. The function returns a record that contains various properties of the value.

Properties of Value.Traits Function

The record returned by the Value.Traits function contains the following properties:

– `Kind`: The data type of the value, such as text, number, date, time, duration, or datetime.

– `Length`: The size of the value in bytes. This property is only applicable to text and binary values.

– `Precision`: The maximum number of digits that the value can hold. This property is only applicable to numeric values.

– `Scale`: The number of digits to the right of the decimal point. This property is only applicable to numeric values.

– `Nullable`: A boolean value that indicates whether the value can be null or not.

– `Values`: A list of distinct values in the column. This property is only applicable to columns.

How to Use Value.Traits Function?

The Value.Traits function can be used in various scenarios. Here are some examples:

Example 1: Check Data Type of a Column

Suppose you have a table that contains a column named “Age” and you want to check its data type. Here is how you can use the Value.Traits function:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSU0lVitWBVErKLElNz8xV4gE", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Age = _t]),

AgeType = Value.Traits(Source[Age]),

AgeKind = AgeType[Kind]

in

AgeKind

This code will return “integer” if the data type of the “Age” column is an integer.

Example 2: Check if a Column is Nullable

Suppose you have a table that contains a column named “Name” and you want to check if it is nullable. Here is how you can use the Value.Traits function:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSU0lVitWBVErKLElNz8xV4gE", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Age = _t]),

NameNullable = Value.Traits(Source[Name]),

NameIsNullable = NameNullable[Nullable]

in

NameIsNullable

This code will return “true” if the “Name” column is nullable.

Example 3: Check Distinct Values in a Column

Suppose you have a table that contains a column named “Gender” and you want to check its distinct values. Here is how you can use the Value.Traits function:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSU0lVitWBVErKLElNz8xV4gE", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Age = _t, Gender = _t]),

GenderValues = Value.Traits(Source[Gender]),

GenderDistinctValues = GenderValues[Values]

in

GenderDistinctValues

This code will return a list of distinct values in the “Gender” column.

The Value.Traits function is a useful tool in Power Query that can help you extract various properties of a value or a column from a table. By using this function, you can enhance your data analysis capabilities and make informed decisions based on the information extracted.

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

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)