topic Re: Generate a Custom Navigation Table with List.Generate in Developer

Generate a Custom Navigation Table with List.Generate

JackSoderstrom — Thu, 09 Sep 2021 22:47:28 GMT

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

Re: Generate a Custom Navigation Table with List.Generate

lbendlin — Fri, 10 Sep 2021 18:32:48 GMT

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.

Re: Generate a Custom Navigation Table with List.Generate

JackSoderstrom — Fri, 10 Sep 2021 18:34:09 GMT

I actually updated my question to this https://community.powerbi.com/t5/Developer/Loop-to-create-table/m-p/2069541#M31670

Re: Generate a Custom Navigation Table with List.Generate

lbendlin — Fri, 10 Sep 2021 18:37:56 GMT

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

Re: Generate a Custom Navigation Table with List.Generate

JackSoderstrom — Fri, 10 Sep 2021 18:51:56 GMT

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?

Re: Generate a Custom Navigation Table with List.Generate

lbendlin — Fri, 10 Sep 2021 18:57:20 GMT

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

Re: Generate a Custom Navigation Table with List.Generate

JackSoderstrom — Fri, 10 Sep 2021 18:56:48 GMT

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.

Re: Generate a Custom Navigation Table with List.Generate

lbendlin — Fri, 10 Sep 2021 19:03:04 GMT

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 ?

Re: Generate a Custom Navigation Table with List.Generate

JackSoderstrom — Fri, 10 Sep 2021 19:09:27 GMT

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

Re: Generate a Custom Navigation Table with List.Generate

lbendlin — Fri, 10 Sep 2021 19:12:08 GMT

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

Re: Generate a Custom Navigation Table with List.Generate

JackSoderstrom — Fri, 10 Sep 2021 19:17:11 GMT

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

Re: Generate a Custom Navigation Table with List.Generate

lbendlin — Fri, 10 Sep 2021 19:35:07 GMT

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

Re: Generate a Custom Navigation Table with List.Generate

JackSoderstrom — Fri, 10 Sep 2021 19:39:14 GMT

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

Re: Generate a Custom Navigation Table with List.Generate

lbendlin — Fri, 10 Sep 2021 19:41:01 GMT

This

Table.ToNavigationTable

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

Re: Generate a Custom Navigation Table with List.Generate

JackSoderstrom — Fri, 10 Sep 2021 20:03:03 GMT

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?

Re: Generate a Custom Navigation Table with List.Generate

lbendlin — Fri, 10 Sep 2021 21:01:18 GMT

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?

Re: Generate a Custom Navigation Table with List.Generate

JackSoderstrom — Fri, 10 Sep 2021 21:15:06 GMT

Can you send both examples?

Re: Generate a Custom Navigation Table with List.Generate

lbendlin — Fri, 10 Sep 2021 21:29:33 GMT

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.

Re: Generate a Custom Navigation Table with List.Generate

Anonymous — Wed, 15 Sep 2021 01:13:01 GMT

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

Re: Generate a Custom Navigation Table with List.Generate

pravinB — Tue, 31 Oct 2023 07:15:06 GMT

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,