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 the Nav table as it goes. Is this possible? Like for example using = List.Generate(() => 1, each _ < 10, each _ + 1)This on shared NavigationTable.Simple = () => let objects = #table( {"Name", "Key", "Data", "ItemKind", "ItemName", "IsLeaf"},{ {"Item1", "item1", #table({"Column1"}, {{"Item1"}}), "Table", "Table", true}, {"Item2", "item2", #table({"Column1"}, {{"Item2"}}), "Table", "Table", true}, {"Item3", "item3", FunctionCallThatReturnsATable(), "Table", "Table", true}, {"MyFunction", "myfunction", AnotherFunction.Contents, "Function", "Function", true} }), NavTable = Table.ToNavigationTable(objects, {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf") in NavTable; shared FunctionCallThatReturnsATable = () => #table({"DynamicColumn"}, {{"Dynamic Value"}});

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.

Generate a Custom Navigation Table with List.Generate

20 Replies

lbendlin
Super User
4 years ago
You need to use the fourth parameter (the selector) to combine the results.

List.Generate( () => [x = 1, y = {}], each [x] < 10, each [x = List.Count([y]), y = [y] & {x}], each [x] )

Just keep in mind that this will result in ever larger chunks of data being handed around on each loop.

alternatively use List.Accumulate or recursive functions.

Please provide sample data in usable format (not as a picture - maybe insert into a table?) and show the expected outcome.
- JackSoderstrom
  Helper I
  4 years ago
  I actually updated my question to this https://community.powerbi.com/t5/Developer/Loop-to-create-table/m-p/2069541#M31670
  - lbendlin
    Super User
    4 years ago
    still hard (for me) to help without sample data and expected outcome.

JackSoderstrom

Helper I

4 years ago

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?

lbendlin

Super User

4 years ago

yes, that helps. what's the structure of DatabaseRecords ?

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.

pravinB
Regular Visitor
2 years ago
Hi, I have multiple APIS, want to create nested navigator based on below scenario
from 1st API I get user & its company details,
from 2nd API get company's User details based on company id which I pass from 1st API
from 3rd API get Assets of Company based on company id which I pass from 1st API,
from 4th API get AsseetDetails based on assetid which I pass from 3rd API,

Want to create navigator Like Below
Companies
Company1
Asset1
Asset1Details
Asset2
Asset2Details
Users
Company2
AssetXYZ
AssetXYZDetails
Asset4ABC
AssetABCDetails
Users

How can I achive this Dynamicaly with help of below refernce
shared NavigationTable.Nested = () as table => let objects = #table( {"Name", "Key", "Data", "ItemKind", "ItemName", "IsLeaf"},{ {"Nested A", "n1", CreateNavTable("AAA"), "Table", "Table", false}, {"Nested B", "n2", CreateNavTable("BBB"), "Table", "Table", false}, {"Nested C", "n3", CreateNavTable("CCC"), "Table", "Table", false} }), NavTable = Table.ToNavigationTable(objects, {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf") in NavTable; CreateNavTable = (message as text) as table => let objects = #table( {"Name", "Key", "Data", "ItemKind", "ItemName", "IsLeaf"},{ {"Item1", "item1", #table({"Column1"}, {{message}}), "Table", "Table", true}, {"Item2", "item2", #table({"Column1"}, {{message}}), "Table", "Table", true} }), NavTable = Table.ToNavigationTable(objects, {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf") in NavTable;

Thanks,
pravinB
Regular Visitor
2 years ago
Hi, I have multiple APIS, want to create nested navigator based on below scenario
from 1st API I get user & its company details,
from 2nd API get company's User details based on company id which I pass from 1st API
from 3rd API get Assets of Company based on company id which I pass from 1st API,
from 4th API get AsseetDetails based on assetid which I pass from 3rd API,

Want to create navigator Like Below
Companies
Company1
Asset1
Asset1Details
Asset2
Asset2Details
Users

Company2
AssetXYZ
AssetXYZDetails
Asset4ABC
AssetABCDetails
Users

How can I achive this Dynamicaly with help of below refernce
shared NavigationTable.Nested = () as table => let objects = #table( {"Name", "Key", "Data", "ItemKind", "ItemName", "IsLeaf"},{ {"Nested A", "n1", CreateNavTable("AAA"), "Table", "Table", false}, {"Nested B", "n2", CreateNavTable("BBB"), "Table", "Table", false}, {"Nested C", "n3", CreateNavTable("CCC"), "Table", "Table", false} }), NavTable = Table.ToNavigationTable(objects, {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf") in NavTable; CreateNavTable = (message as text) as table => let objects = #table( {"Name", "Key", "Data", "ItemKind", "ItemName", "IsLeaf"},{ {"Item1", "item1", #table({"Column1"}, {{message}}), "Table", "Table", true}, {"Item2", "item2", #table({"Column1"}, {{message}}), "Table", "Table", true} }), NavTable = Table.ToNavigationTable(objects, {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf") in NavTable;

Thanks,

Forum Discussion

Generate a Custom Navigation Table with List.Generate

20 Replies

Related content

List.Generate, make behave like Accumulate or XL REDUCE

Dynamic Date Offset Navigator

Generating Missing Data

Page Navigation

Navigation Buttons Missing