Type.IsNullable

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Type.IsNullable

Understanding the Type.IsNullable Function

The Type.IsNullable function is used to identify whether a value in a column can be null or not. It takes a parameter that represents the type of the value, and returns a Boolean value indicating whether the value can be null or not. The syntax of the Type.IsNullable function is as follows:

Type.IsNullable(type as type) as logical

The ‘type’ parameter represents the data type of the value, and the function returns true if the type can be null, and false if the type cannot be null.

The M Code Behind the Type.IsNullable Function

The Type.IsNullable function is implemented using M code, which is a functional programming language used by Power Query. The M code behind the Type.IsNullable function is as follows:

(type) =>

let

nullableTypes = {

type nullable any,

type nullable binary,

type nullable date,

type nullable datetime,

type nullable datetimezone,

type nullable duration,

type nullable time,

type nullable text

},

isNullable = List.Contains(nullableTypes, type)

in

isNullable

The code is written as a lambda function that takes a ‘type’ parameter and returns the ‘isNullable’ value. The code uses a ‘let’ statement to define a list of nullable data types. The ‘List.Contains’ function is then used to check whether the ‘type’ parameter is present in the list of nullable types. If the ‘type’ parameter is present in the list, then the ‘isNullable’ value is set to true. Otherwise, the ‘isNullable’ value is set to false.

Usage of the Type.IsNullable Function

The Type.IsNullable function is primarily used to identify whether a column in a dataset can have a null value. For example, suppose you have a dataset that contains customer information. One of the columns in the dataset represents the customer’s phone number. If this column can have a null value, you can use the Type.IsNullable function to identify it. The following M code demonstrates how to use the Type.IsNullable function:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjQ2MjIwMjAxM6k0MrQ2tjY2MjI2sDQwNDE1MjGyNLG1MDbRNgDQwNjQ0NLYwMCjVcjIzNjA0sza3MTQyMA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t, #"Phone Number" = _t]),

IsNullable = Table.AddColumn(Source, "Is Phone Number Nullable", each Type.IsNullable(Value.Type([Phone Number]))),

#"Changed Type" = Table.TransformColumnTypes(IsNullable,{{"Customer Name", type text}, {"Phone Number", type text}, {"Is Phone Number Nullable", type logical}})

in

#"Changed Type"

In the code above, we first create a table from a JSON document containing customer information. We then use the Table.AddColumn function to add a new column named ‘Is Phone Number Nullable’ to the table. We use the Type.IsNullable function to determine whether the ‘Phone Number’ column can have a null value. Finally, we transform the data types of the columns in the table using the Table.TransformColumnTypes function.

The Type.IsNullable function is a powerful M function that enables users to identify whether a column in a dataset can have a null value. The M code behind the Type.IsNullable function uses a list of nullable data types to determine whether a value can be null or not. Users can use the function to write customized data transformations in Power Query and other BI tools.

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)