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

What is Type.NonNullable?

Type.NonNullable is a function in Power Query that returns the non-nullable version of a given type. In simpler terms, it returns the type of a value with nullability information removed. For example, if you have a column of data that contains null values, you can use Type.NonNullable to create a new column that removes those null values.

The M Code Behind Type.NonNullable

The M code behind Type.NonNullable is fairly simple. Here’s what it looks like:

(Type as nullable type) as type =>

if Type.IsNullable(Type) then




Let’s break this down. The first line defines the function and takes a single parameter, Type, which is a nullable type. The second line checks whether the type is nullable using the Type.IsNullable function. If it is, it uses Type.NonNullable to return the non-nullable version of the type. If it’s not nullable, the function simply returns the original type.

Examples of Type.NonNullable in Action

Now that we know what Type.NonNullable does and have seen the M code behind it, let’s take a look at some examples of how it can be used.

Example 1: Removing Null Values from a Column

Suppose you have a table with a column named “Salary” that contains both numeric values and null values. If you want to create a new column that removes the null values, you can use Type.NonNullable like this:

= Table.AddColumn(Source, "Salary Non-Nullable", each Type.NonNullable(Value.Type([Salary])))

This will create a new column called “Salary Non-Nullable” that has the same data as the “Salary” column, but with the null values removed.

Example 2: Using Type.NonNullable with List.Combine

Another use case for Type.NonNullable is when you need to combine multiple lists that may or may not contain null values. In this case, you can use the List.Combine function in conjunction with Type.NonNullable to remove the null values before combining the lists. Here’s an example:

= List.Combine(


{ListA, ListB, ListC},

each List.Select(_, each _ <> null)


each Type.NonNullable(Value.Type(_))


This code will combine the lists ListA, ListB, and ListC, removing any null values in each list before combining them. The resulting list will also have nullability information removed.

In conclusion, Type.NonNullable is a useful function in Power Query that can help you work with data that contains null values. By removing the nullability information from a type, you can create new columns or combine lists without having to worry about null values. We hope this article has given you a better understanding of how Type.NonNullable works and how it can be used.

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

Upcoming Courses

Contact Us


    Your Name (required)


    Email (required)


    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)