cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JackSoderstrom
Helper I
Helper I

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 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"}});
18 REPLIES 18
JackSoderstrom
Helper I
Helper I

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")
    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.

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)"

 

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

lbendlin_0-1631301120631.png

 

 

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

Hi @JackSoderstrom,

Did lbendlin 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements to find them more quickly.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

the icons for your top and next levels should be folder, no?  I think you didn't specify ItemKind accordingly.  Should be Folder>Folder>Table  (or something else for the top level)

 

Handling navigation for Power Query connectors | Microsoft Docs

Yeah, I changed it right now. But I believe its simply a change cosmetic

This 

Table.ToNavigationTable

function is a bit pompous. I think it will be easier to hand craft the code.

I mean does that function matter?

 

        objects = #table(
            {"Name",         "Key",   "Data",                                                    "ItemKind", "ItemName", "IsLeaf"},{

            {ColumnNames{0}, ColumnNames{0}, Table.Column(DatabaseRecords(num), ColumnNames{1}), "Table",    "Table",    true}
        }),
        NavTable = Table.ToNavigationTable(objects, {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")

 

This just needs the ability to iterate through 1-any number with a loop. We create "NavTable" and then after that we can combine it with a table and then keep on repeating that right?

Your "Data"  column needs to point to an actual table, or a function.

 

I see that you use the same information for Key and Name but a different source for ItemName.  I need to adjust my sample source table accordingly.  Do you want the two hierarchy levels in the same table or separately?

 

 

Can you send both examples?

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.

 

lbendlin_0-1631309365310.png

 

lbendlin
Super User III
Super User III

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.

still hard (for me) to help without sample data and expected outcome.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.