In “Data Types in Power Query” post we saw what types exist in Power Query. Let’s see now what functions Power Query has that can deal with types. Most of such functions are classified in “Type” group, but the important ones are placed in “Value” group.
In the example bellow, the first column has data of different types. All other columns are created by using some Power Query functions, except the column “TypeName” which is created by using our custom function. Name of each column is the same as the name of a function used.
Value.Type(value as any) as type
Value.Type function receives the values from the first column and then returns its type. In Power Query types are presented only by green word “Type”, and those results in second column don’t help us much. In order to reveal what type each value really is, we will use our custom function “TypeName”. You can find that function in sample file bellow or you can see it in this post. This function will take types from the second column and for each “Type”, it will reveal its correct type in the third column. Now we can see all the different types (type null, type text, type date…).
|Value.Type( [Data] ) |
TypeName( [Value.Type] )
Type.Is( [Value.Type],type number )
Value.As( [Data], type number )
Value.Is( [Data], type number )
Type.Is(type1 as type, type2 as type) as logical
Type.Is function will compare two types and will return TRUE or FALSE. In our example we compared types from the column Value.Type with type number. Obviously, only cell in eighth row will return TRUE, because 999 is a number ✶.
Value.As(value as any, type as type) as any
Value.As function is returning value from the first argument, but only if type of that value is compatible with the type that is second argument. If they are not compatible, this function will return error. Because we used type number as second argument, only the cell in eighth row will return 999 , all other cells will return Error.
Value.Is(value as any, type as type) as logical
Value.Is function is comparing type of some value with the type that is the second argument. It will return TRUE or FALSE. In our example, we compare types for values in the first column with type number. This is the reason that only cell in eighth row will return TRUE .
Is and As Operators
If we don’t want to hard code our data types, then we have to use Value.As and Value.Is functions. If hard coding our types is OK, then we can use is and as operators. Expressions on the right side will create the same two last columns as in example above.
|Not Hard Coded||Hard Coded|
|Value.As( [Data], type number )||[Data] as number|
|Value.Is( [Data], type number )||[Data] is number|