Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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"}});
1 ACCEPTED SOLUTION

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

 

View solution in original post

20 REPLIES 20
pravinB
Regular Visitor

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

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,



 

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
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.