Type.NonNullable

Updated on

Type.NonNullable is a Power Query M function that extracts the non-nullable type from a given type. The function returns the non-nullable version of the input type.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Type.NonNullable( type as type ) as type

Description

The Type.NonNullable function in Power Query M is designed to transform nullable data types into their non-nullable counterparts. It accepts a data type as input, specifically types that are marked as nullable, such as type nullable text or type nullable number. The function then removes the nullable attribute from these types, effectively converting them into non-nullable types.

For instance, applying Type.NonNullable to type nullable date will output type date. In cases where the input is an abstract type like any or a non-nullable type, the function will output a corresponding non-nullable type or maintain the original type, respectively.

Examples

Let’s look at a few examples of how this works.

Transforming Nullable Primitive Types

The primary function of Type.NonNullable is to remove the nullable attribute from primitive data types. This is particularly useful when you need to control null values. Consider the following examples:

Type.NonNullable( type nullable date )     // Output: type date
Type.NonNullable( type nullable list )     // Output: type list
Type.NonNullable( type nullable function ) // Output: type function

Here, the Type.NonNullable function effectively strips away the nullable aspect from list and function types, ensuring they are treated as non-nullable.

Handling Abstract Types

Now suppose you provide the function with an abstract type like any, anynonnull or none. The Type.NonNullable function then outputs a type that supports all values of the original type, except for the null values.

Type.NonNullable( type any )  // Output: type anynonnull
Type.NonNullable( type none ) // Output: type none
Type.NonNullable( type null ) // Output: type none

In these examples, the function outputs a type that aligns with the original’s non-nullable nature.

Preserving Nonnullable Types

When the original type provided to the Type.NonNullable function is already non-nullable, the output remains unchanged. In other words, the function recognizes and preserves non-nullable types.

Type.NonNullable( type anynonnull ) // Output: type anynonnull
Type.NonNullable( type list )       // Output: type list
Type.NonNullable( type logical )    // Output: type logical

These examples demonstrate the function’s consistent behaviour with already non-nullable types.

Other functions related to Type.NonNullable are:

BI Gorilla Blog

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/type-nonnullable