Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
jcountryman
Helper I
Helper I

How To Dynamically Define Table Types?

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)}
        }
    )

 

 

 

1 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)}
        }
    )

 

 

View solution in original post

3 REPLIES 3
AlexisOlson
Super User
Super User

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)

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)}
        }
    )

 

 

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

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors