Value.Is

Updated on

Value.Is is a Power Query M function that determines whether an input value is compatible with the specified type. The function returns a boolean indicating the compatibility between the value and the input type.

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

Syntax

Value.Is(
   value as any,
   type as type,
) as logical

Description

The Type.Is function determines if a value of type1 is always compatible with type2. The function accepts:

  • an arbitrary type as first argument.
  • a nullable primitive type value as second argument.

The function checks whether the data type provided in argument 1 can always be treated as the type provided in argument 2. In this comparison it’s important to note that type equivalence of complex data types is not supported.

Examples

Let’s look at some examples on how the Type.Is function works.

Basic comparison

The order of the argument is important when setting up your tests. Let’s say you want to test whether one type of value can always be treated as another.

The following expression tests whether a value of type number can also be treated as type any.

Type.Is( type number, type any ) // output: true 

If you swap the arguments around, you are essentially testing whether a value of type any can always be treated as the type number. This is not the case.

Type.Is( type any, type number ) // output: false

You can see similar behavior when testing a primitive type with a nullable primitive type. Depending on whether you provide its value in the first or second position in the comparison, the outcome is different.

Type.Is(type text, type nullable text)  // true 
Type.Is(type nullable text, type text)  // false 

A value of type text can always be treated as ‘nullable text’. However, a nullable text cannot always be treated as a text. After all, null values can also appear.

Comparing complex data types

Then we get to the topic of the more complex custom data types. M does not support determining compatibility of a given type with a custom type.

Knowing this, we can verify that a custom record data type can conform to a record type.

Type.Is( type [a=text], type record )  // true 

After all, no matter what you define inside a custom data type, it still is a record.

However, the Type.Is function does not support testing whether a custom data type equals that same data type.

Type.Is( type [a=text], type [a=text] ) // Output: false

For more complex compatibility testing of custom data types, you can create your own by extracting the characteristic of a custom type. You can build your own compatibility test by using functions like Type.ListItem, Type.NonNullable, Type.RecordFields, Type.TableRow, Type.FunctionParameters, Type.FunctionRequiredParameters and Type.FunctionReturn.

For example, the bottom of this page specifies the following statements:

Type.ListItem( type {number} )   // type number 
Type.NonNullable( type nullable text ) // type text 
Type.RecordFields( type [A=text, B=time] ) 
  // [ A = [Type = type text, Optional = false], 
  //   B = [Type = type time, Optional = false] ] 
Type.TableRow( type table [X=number, Y=date] ) 
  // type [X = number, Y = date] 
Type.FunctionParameters(
        type function (x as number, optional y as text) as number) 
  // [ x = type number, y = type nullable text ] 
Type.FunctionRequiredParameters(
        type function (x as number, optional y as text) as number) 
  // 1 
Type.FunctionReturn(
        type function (x as number, optional y as text) as number) 
  // type number 

Other functions related to Value.Is are:

BI Gorilla Youtube Channel

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