Forum Discussion
Generate a Custom Navigation Table with List.Generate
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.
Ok, Im trying to make this dynamic
CreateNavTable = (num) as table =>
let
ColumnNames = Table.ColumnNames(DatabaseRecords(num)),
objects = #table(
{"Name", "Key", "Data", "ItemKind", "ItemName", "IsLeaf"},{
{ColumnNames{0}, ColumnNames{0}, Table.Column(DatabaseRecords(num), ColumnNames{1}), "Table", "Table", true},
{ColumnNames{1}, ColumnNames{1}, Table.Column(DatabaseRecords(num), ColumnNames{1}), "Table", "Table", true},
{ColumnNames{2}, ColumnNames{2}, Table.Column(DatabaseRecords(num), ColumnNames{2}), "Table", "Table", true},
{ColumnNames{3}, ColumnNames{3}, Table.Column(DatabaseRecords(num), ColumnNames{3}), "Table", "Table", true},
{ColumnNames{4}, ColumnNames{4}, Table.Column(DatabaseRecords(num), ColumnNames{4}), "Table", "Table", true},
{ColumnNames{5}, ColumnNames{5}, Table.Column(DatabaseRecords(num), ColumnNames{5}), "Table", "Table", true},
{ColumnNames{6}, ColumnNames{6}, Table.Column(DatabaseRecords(num), ColumnNames{6}), "Table", "Table", true},
{ColumnNames{7}, ColumnNames{7}, Table.Column(DatabaseRecords(num), ColumnNames{7}), "Table", "Table", true},
{ColumnNames{8}, ColumnNames{8}, Table.Column(DatabaseRecords(num), ColumnNames{8}), "Table", "Table", true},
{ColumnNames{9}, ColumnNames{9}, Table.Column(DatabaseRecords(num), ColumnNames{9}), "Table", "Table", true},
{ColumnNames{10}, ColumnNames{10}, Table.Column(DatabaseRecords(num), ColumnNames{10}), "Table", "Table", true},
{ColumnNames{11}, ColumnNames{11}, Table.Column(DatabaseRecords(num), ColumnNames{11}), "Table", "Table", true},
{ColumnNames{12}, ColumnNames{12}, Table.Column(DatabaseRecords(num), ColumnNames{12}), "Table", "Table", true}
}),
NavTable = Table.ToNavigationTable(objects, {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
in
NavTable;
This currently makes 12 Nav Tables in the data section that are sub nav tables. CreateNavTable is called
shared Navigation = () =>
let
objects = #table(
{"Name", "Key", "Data", "ItemKind", "ItemName", "IsLeaf"},{
{NameOfDatabase(0), DatabaseID(0), CreateNavTable(0), "Table", "Table", false},
{NameOfDatabase(1), DatabaseID(1), CreateNavTable(1), "Table", "Table", false}
}),
NavTable = Table.ToNavigationTable(objects, {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
in
NavTable;
Well, I will be calling "CreateNavTable" with many different numbers. It won't always have 12 subtables. Maybe more, maybe less. So instead of CreateNavTable being hard codes to creating 12. I need to be able to loop with a parameter in the function. I can find the amount I need to loop through just fine. I just need to get the "List.Generate or list.Accumulate" working. I'm not sure how though. Hopefully, this clears things up?
yes, that helps. what's the structure of DatabaseRecords ?
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") inIve 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.
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 ?
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)"