Forum Discussion
Solved
Dynamically generate column schema as record
Is it possible to generate dynamically for all columns of a given table the following
[id=nullable Int64.Type, year=nullable Int64.Type, amount=nullable number, ing_est=nullable datetime] ...
Now I probably see what you want to achive. You want to replace this part of Table.Group. Is that correct?
If yes, you can just delete that part of Table.Group and use Table.Combine to preserve column types, like this:
let src=Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZBBCsMwDAT/4nMiVivLtvSO3EL+/4267aUlhWJdBIJhR3ueZUSUrRD0uXaFjjCprfX3te6IHfVAJDxJ0WaYU67tg60v1jkqhCugzYXlJOuNTuntm1RPjGQVRL+Tr//oGOai3WyF/aGpI9GTsytiRdOPmaNMhlhd1PzH3jT9UOQslE08FjVnzpN0ib6seWevBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, year = _t, amount = _t, ing_est = _t]), ct = Table.TransformColumnTypes(src,{{"id", Int64.Type}, {"year", Int64.Type}, {"amount", type number}, {"ing_est", type datetime}}, "en-US"), GroupedRows = Table.Group(ct, {"id"}, {{"ad", each _, type table}}), Combined = Table.Combine(GroupedRows[ad]) in Combined
Hi smpa01, check this:
Output:
let
src=Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZBBCsMwDAT/4nMiVivLtvSO3EL+/4267aUlhWJdBIJhR3ueZUSUrRD0uXaFjjCprfX3te6IHfVAJDxJ0WaYU67tg60v1jkqhCugzYXlJOuNTuntm1RPjGQVRL+Tr//oGOai3WyF/aGpI9GTsytiRdOPmaNMhlhd1PzH3jT9UOQslE08FjVnzpN0ib6seWevBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, year = _t, amount = _t, ing_est = _t]),
ct = Table.TransformColumnTypes(src,{{"id", Int64.Type}, {"year", Int64.Type}, {"amount", type number}, {"ing_est", type datetime}}, "en-US"),
GroupedRows = Table.Group(ct, {"id"}, {{"ad", each _, type table}, {"Schema", each Record.FromTable(Table.RenameColumns(Table.Schema(_)[[Name], [TypeName]], {{"TypeName", "Value"}}, MissingField.Ignore)), type record}})
in
GroupedRows
dufoq3 this is good but I want to dynamically pass on that bit to finally give me the following
//didnot work let src=Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZBBCsMwDAT/4nMiVivLtvSO3EL+/4267aUlhWJdBIJhR3ueZUSUrRD0uXaFjjCprfX3te6IHfVAJDxJ0WaYU67tg60v1jkqhCugzYXlJOuNTuntm1RPjGQVRL+Tr//oGOai3WyF/aGpI9GTsytiRdOPmaNMhlhd1PzH3jT9UOQslE08FjVnzpN0ib6seWevBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, year = _t, amount = _t, ing_est = _t]), ct = Table.TransformColumnTypes(src,{{"id", Int64.Type}, {"year", Int64.Type}, {"amount", type number}, {"ing_est", type datetime}}, "en-US"), GroupedRows = let x = Table.Group(ct, {"id"}, {{"ad", each _, type table}, {"Schema", each Record.FromTable(Table.RenameColumns(Table.Schema(_)[[Name], [TypeName]], {{"TypeName", "Value"}}, MissingField.Ignore)), type record}})[Schema]{0} in Table.Group(ct, {"id"}, {{"ad", each _, type table x}}) in GroupedRowsWhat is the purpose of this? Do you want to replase table types with another table types or what? If yes, there is more efficient way:
let Source = #table(type table[Name=text, Age=Int64.Type, Date of Birth=date], {{"Peter", 20, #date(2000,10,15)}, {"Martin", 25, #date(2003,12,10)}}), ChangedType = Table.TransformColumnTypes(Source,{{"Age", type number}, {"Date of Birth", type datetime}}), RestoredTypes = Value.ReplaceType(ChangedType, Value.Type(Source)) in RestoredTypesThe purpose is to create a function that can group any table with the dynamically generated schema of that table to be passed on to Table.Group.
