Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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"}});
Solved! Go to 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.
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
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,
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
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
Yeah that should work. Here is the example of 12 hard-coded 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
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.
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.
I actually updated my question to this https://community.powerbi.com/t5/Developer/Loop-to-create-table/m-p/2069541#M31670
still hard (for me) to help without sample data and expected outcome.
User | Count |
---|---|
13 | |
5 | |
2 | |
1 | |
1 |
User | Count |
---|---|
24 | |
3 | |
3 | |
2 | |
2 |