How To Dynamically Define Table Types?

[jcountryman]

Is there a way to change a list of paired column names and types into a table type? Or maybe way to manipulate types?

let
    my_list = { //ideally, the types would be *actual* types, not text
      {"UNIQUE_ID", "Number.Type"},
      {"NAME", "Text.Type"},
      {"START_DATE", "Date.Type"}
    },
    
    my_goal = //I want to transform my_list into this:
    type table[
        UNIQUE_ID = Number.Type,
        NAME = Text.Type,
        START_DATE = Date.Type
    ],
    
    //But isn't there a better way than this?
    #"My List As Table Type" = Expression.Evaluate(
        "type table[#(lf)" & Text.Combine(
            List.Transform(
                my_list,
                each "#(tab)"& _{0} &"="& _{1}
            ),
            ",#(lf)"
        )&"#(lf)]",
        #shared
    )
in
    #table(
        #"My List As Table Type",
        {
            {1, "Andy", #date(2010,03,25)},
            {2, "Sami", #date(2008,06,09)},
            {3, "Max", #date(2005,11,03)}
        }
    )

[AlexisOlson]

If they are actual types, then Table.TransformColumnTypes does a nice job.

let
    my_list =
        {
            {"UNIQUE_ID", Number.Type},
            {"NAME", Text.Type},
            {"START_DATE", Date.Type}
        },
    T1 = #table(
        List.Transform(my_list, each  _{0}),
        {
            {1, "Andy", #date(2010,03,25)},
            {2, "Sami", #date(2008,06,09)},
            {3, "Max", #date(2005,11,03)}
        }
    )
in
   Table.TransformColumnTypes(T1, my_list)

If they are text, then you can transform to types before using them.

let
    my_list =
        {
            {"UNIQUE_ID", "Number.Type"},
            {"NAME", "Text.Type"},
            {"START_DATE", "Date.Type"}
        },
    my_types = List.Transform(
        my_list, each {_{0}, Expression.Evaluate(_{1}, #shared)}
    ),
    T1 = #table(
        List.Transform(my_list, each  _{0}),
        {
            {1, "Andy", #date(2010,03,25)},
            {2, "Sami", #date(2008,06,09)},
            {3, "Max", #date(2005,11,03)}
        }
    )
in
   Table.TransformColumnTypes(T1, my_types)

[jcountryman · Accepted solution]

Thank you!

I'm sorry I wasn't clear on this point, but I specifically needed to get the table type so I could re-use it in later steps. I figured out a method that doesn't use Expression.Evaluate by using List.Accumulate to generate an empty table with typed fields, and wrapping that in Value.Type:

let
    my_list = {
      {"UNIQUE_ID", Number.Type},
      {"NAME", Text.Type},
      {"START_DATE", Date.Type}
    },
    
    #"My List As Table Type" = Value.Type(
        List.Accumulate(
            my_list,
            #table({},{}), //seed with empty table
            (return_table, list_item)=>Table.AddColumn(
                return_table,
                list_item{0},
                each null, //no data, just headers
                list_item{1}
            )
        )
    )
in
    #table(
        #"My List As Table Type",
         {
            {1, "Andy", #date(2010,03,25)},
            {2, "Sami", #date(2008,06,09)},
            {3, "Max", #date(2005,11,03)}
        }
    )

[AlexisOlson]

I still think Table.TransformColumnTypes is useful.

let
    my_list =
        {
            {"UNIQUE_ID", Number.Type},
            {"NAME", Text.Type},
            {"START_DATE", Date.Type}
        },
    my_cols = List.Transform(my_list, each _{0}),
    my_table_type = Value.Type(
            Table.TransformColumnTypes(#table(my_cols,{}), my_list)
        ),
    T1 = #table(
        my_table_type,
        {
            {1, "Andy", #date(2010,03,25)},
            {2, "Sami", #date(2008,06,09)},
            {3, "Max", #date(2005,11,03)}
        }
    )
in
   T1