Forum Discussion

smpa01's avatar
smpa01
Community Champion
1 year ago
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]

 

 

 

sample query

 

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}}),
    #"Grouped Rows" = Table.Group(ct, {"id"}, {{"ad", each _, type table [id=nullable Int64.Type, year=nullable Int64.Type, amount=nullable number, ing_est=nullable datetime]}})
in
    #"Grouped Rows"

 

 

AlexisOlson 

Need Help
  • dufoq3's avatar
    dufoq3
    1 year ago

    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

10 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    1 year ago

    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

     

    • smpa01's avatar
      smpa01
      Community Champion
      1 year ago

      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
    GroupedRows

       

       

      • dufoq3's avatar
        dufoq3
        Community Champion
        1 year ago

        What 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
    RestoredTypes