Forum Discussion

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
2 years 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] ...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years 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
smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
2 years ago

The 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.

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
2 years ago

Could you provide expected result based on sample data please?

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    2 years ago

    this is the end result I want to achieve once the schema is dynamically generated and passed on to Table.Group

     

     

    The challenge is to dynamically somehow replace the following portion

    [id=nullable Int64.Type, year=nullable Int64.Type, amount=nullable number, ing_est=nullable datetime]

     with a function call in the code below

    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]}}),
    #"Expanded ad" = Table.ExpandTableColumn(#"Grouped Rows", "ad", {"id", "year", "amount", "ing_est"}, {"id.1", "year", "amount", "ing_est"})
in
    #"Expanded ad"