Data Types in Power Query

BuiltIn Types

Power Query has built-in record with identifier #shared. This record will give us all identifiers that exist in Power Query in current context, and their types. We will transform this record into table and we will then remove this query itself from the list of identifiers, in order to avoid cyclic reference. We will filter only identifiers that represent some of Power Query types. At the end we will sort by type name.

    IdentifiersAndTypes = Record.ToTable( #shared )   
    , RemoveItself = Table.SelectRows(IdentifiersAndTypes, each [Name] <> "Query1" )    
    , FilterOnlyTypes = Table.SelectRows( RemoveItself, each Value.Type( [Value] ) = Type.Type )   
    , SortTypes = Table.Sort( FilterOnlyTypes, { "Name", Order.Ascending })
#Shared (1) returned all identifiers in Power Query, together with their types. We will delete (2) the name of our query from this list in order to avoid cyclic reference. We will filter only identifiers that refer to Power Query types (3).

Result will show us, that there are 63. built in types in Power Query. They are presented below.


Primitive types

Most of those types above are so called “ascribed types”. It means that we can declare them, Power Query will remember them, but it will not enforce them. Power Query only enforce primitive types. This is list of primitive types in Power Query:

type anytype anynonnulltype binarytype datetype datetimetype datetimezone
type durationtype functiontype listtype logicaltype nulltype none
type numbertype recordtype tabletype texttype timetype type
If we try to define a function, arguments can only be declared as primitive types. We can see on the image that only primitive types any, binary, text are colored green by intelisense (1). Even the longer syntax like “Any.Type” is not acceptable. All other types (2) are incorrect and can not be used.
It is the same if we want to use “is” operator. This operator will only work with primitive types ( number is green by intelisense (1) ). Even the longer syntax “Number.Type” will not work. We have to type exactly “number.
If we select the cell with “Decimal.Type” type in our #shared table, we will see in graphic interface that this type is actually of “number” type (1). It is the same for many other types “Double.Type”, “Int16.Type”, “Int64.Type”, “Percentage.Type” etc., “number” is their real type.

We can create a function that will return primitive type for each of our 63. built in types in Power Query. This is the function:

( TypeToName as type ) as text =>
      ListOfTypes = { type any, type anynonnull, type binary, type date, type datetime
        , type datetimezone, type duration, type function, type list, type logical
        , type null, type number, type record, type table, type text, type time, type type }
    , ListOfTypeNames = { "type any", "type anynonnull", "type binary", "type date"
        , "type datetime", "type datetimezone", "type duration", "type function", "type list"
        , "type logical", "type null", "type number", "type record", "type table", "type text"
        , "type time", "type type" }
    , ZipedTypes = List.Zip( { ListOfTypes, ListOfTypeNames } )
    , NameForType = List.Select( ZipedTypes, each  Type.Is( _{0}, TypeToName ) ){0}{1}
By using the function above, we will create column “Primitive types”. We can see that for each built in type (1), we can get one primitive type (2). This is true for 59/63 built in types. For some built in types (3), we will get Error. In graphic interface we can see that such types are either record or function (4).

The reason for Error in image above is in the fact that table, function and record types are abstract types. There is no value that has type of table, function of record. We can create one table, record and function and compare their types with such abstract types, but the result will never be TRUE.

      Table = #table( { "Column" }, { { "Value" } } ) 
    , Record = [Field="Value"]
    , Function = () => let Result = "Value" in Result    
    , IsTableType = Value.Type( Table ) = type table
    , IsRecordType = Value.Type( Record ) = type record 
    , IsFunctionType = Value.Type( Function ) = type function 
    , IsTableTypeCompatible = Type.Is( Value.Type( Table ), type table )
    [ IsTableType=IsTableType, IsRecordType=IsRecordType
    , IsFunctionType=IsFunctionType, IsTableTypeCompatible=IsTableTypeCompatible ]
We can see that equality will never be reached (1). But if we use “Type.Is” function to check compatibility with primitive types we will get TRUE. Compatibility means that any table is compatible with the “type table”. The same is true for records and functions.

When we declare type for function arguments, that argument will accept any value that is compatible with our declaration. If our argument is of type “number”, such argument can receive values of all the compatible types “Double.Type”, “Int16.Type”, “Int64.Type”, “Percentage.Type” etc.

How to check ascribed type

We will create three values of type Int64.Type, Decimal.Type and Text.Type. The question is how to read ascribed types of this values. We can do that from “metadata”.

      Int64Type = Value.ReplaceType( 99, Int64.Type )
    , DecimalType = Value.ReplaceType( 99.99, Decimal.Type )
    , TextType = Value.ReplaceType( "hundred", Text.Type )
    , OneTable = #table( { "values" }, { { Int64Type }, { DecimalType }, { TextType } } )
    , AddMetadata = Table.AddColumn( OneTable, "Metadata"
        , each Value.Metadata( Value.Type( [values] ) ) )
    , AddAscribedTypeName = Table.AddColumn( AddMetadata, "AscribedTypeName"
        , each [Metadata][Documentation.Name] )
Value.Metadata function will return record which has Documentation.Name fild which contains ascribed type name. This is where Power Query store information about ascribed types.

Sample file can be downloaded here:

Leave a Comment

Your email address will not be published. Required fields are marked *