Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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)}
}
)
Solved! Go to 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)}
}
)
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
Check out the July 2025 Power BI update to learn about new features.