Forum Discussion

JackSoderstrom's avatar
JackSoderstrom
Helper I
4 years ago
Solved

Generate a Custom Navigation Table with List.Generate

I'm trying to create a Dynamic Nav table with a Custom Connector. How do I use List.Generate() and Navigation table together to create for example 10 tables that iterate through the loop and add to t...
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    Here is a proof of concept for the child level

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsIzgLGM4ywTMcgKpM0AwDRFMIwTTGMFE0maqFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each #table({"Name","Key","Data","ItemKind","ItemName","IsLeaf"},{{[Column2],{[Column2]},"your function here " & [Column2],"Table",[Column2],true}})),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Column1"}, {{"Group", each _, type table [Custom=table]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Children", each Table.Combine([Group][Custom]))
in
    #"Added Custom1"

     

    You would still need to call the NavTable function on the "Children"  tables and then on the "Column1"  tables to tie it together, but you can already see the dynamic approach.

     

     

JackSoderstrom's avatar
JackSoderstrom
Helper I
4 years ago
    let

        ColumnNames   = Table.ColumnNames(DatabaseRecords(num)),
        ListToIterate = List.Numbers(0, 2),

        objects = List.Accumulate(ListToIterate, 0, (state, current) => #table({"Name", "Key",   "Data", "ItemKind", "ItemName", "IsLeaf"},{ {ColumnNames{current}, ColumnNames{current}, Table.Column(DatabaseRecords(num), ColumnNames{current}), "Table",    "Table",    true} })),
        //objects2 = List.Accumulate(ListToIterate, 0, (state, current) => #table({"Name", "Key",   "Data", "ItemKind", "ItemName", "IsLeaf"},{ {ColumnNames{current}, ColumnNames{current}, Table.Column(DatabaseRecords(num), ColumnNames{current}), "Table",    "Table",    true} })),

        //combined = Table.Combine({objects, objects2}),

        CombinedNavTable = Table.ToNavigationTable(objects, {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
    in

Ive tried something like this. This will return the 2nd element. Ive also been able to create two tables with this format and use the "Combine" function for tables. It works but I cant seem to automate it with a loop.

lbendlin's avatar
lbendlin
Super User
4 years ago

For the source data should I assume a two column table with a mild hierarchy, say one parent has 5 children and the other 12 ?

  • JackSoderstrom's avatar
    JackSoderstrom
    Helper I
    4 years ago

    Yes it will all be dynamic and changing, So for example how I call CreateNavTable(0). I will be putting a parameter so for example if I have 50 sub tables that I need the function will read "CreateNavTable(0 , 50)"

     

    • lbendlin's avatar
      lbendlin
      Super User
      4 years ago

      I was more thinking along the lines of "here's a table with a bit of hierarchy, make a nav table (of nav tables) out of it"

       

      Like this

       

       

      • JackSoderstrom's avatar
        JackSoderstrom
        Helper I
        4 years ago

        Yeah that should work. Here is the example of 12 hard-coded in.