Power Query function for using a control table to build a list of type transformations to pass to Table.TransformColumnTypes

powerquery-GetTypeTransforms.m

(typeTable as table) as list
    =>
    let
        //check that the columns in the supplied table have the expected names
        checkColumnNames = Table.ColumnNames(typeTable) = {"ColumnName","Type"},

        /*
        primitive types use this format in TransformColumnTypes
        type typeName
        Listed here are most common formats - full list on this page:
        https://learn.microsoft.com/en-us/powerquery-m/power-query-m-type-system
        */
        primitiveNames = {"true/false","decimal number","time","date","date/time","text"},
        primitives = {type logical, type number, type time, type date, type datetime, type text},

        /*
        So called type claim facets, which are actually representations of the number type. 
        The format passed into Table.TransformColumns is:
        subtypeName.Type
        Common formats listed here. Full list at the link in the comment above.
        */
        subtypeNames = {"whole number","currency","percentage"},
        subtypes = {Int64.Type,Currency.Type,Percentage.Type},

        //combine the four lists into two lists 1) of text names and 2) of the type values
        typeNames = primitiveNames & subtypeNames,
        types = primitives & subtypes,

        //convert the text names into type values
        columnTypes = 
                    List.Transform(
                        typeTable[Type],
                        each try types{List.PositionOf(typeNames,Text.Lower(_))} otherwise type any
                    ),

        //combine the column names with the type values into a list of lists
        Result
            = List.Zip(
                {
                    typeTable[ColumnName] ,
                    columnTypes
                }
            )
    in
        /*
        if the structure of the table passed to this function was as expected, then return the result
        otherwise return an empty list
        */
        if checkColumnNames then Result else {}