Forum Discussion
Custom Connector and Navigation Tables
What you need to do is use Table.View, it is very powerful as it lets you reinterpate any code that transforms or drills into a table. In paticular you want to include a defination for the handler OnSelectRows:
View= (state) =>
Table.View(null,
[
GetType = () => ...,
GetRows = () => ...,
OnSelectRows = (selector) =>
let
condition = RowExpression.From(selector),...,
in
@View(newState)
])
The selector will be the abstract syntax tree of the query. If the code returns an error, then the view is handled by default logic and the next higher view in the stack will be tried.
First, make sure you read: https://docs.microsoft.com/en-us/power-query/handlingnavigationtables
You will need to copy the Table.ToNavigationTable to your connector code.
Then you would make a function like this:
//listFunction () as {text} -- Produces a list of items the user can select.
//dataFunction (value as text) as table -- Produces the data for a selected item
NavigationTableFromList = (dataFunction as function, listFunction as function, optional isLeaf as logical) as table =>
let
_isLeaf = if (isLeaf = null) then true else isLeaf,
itemKind = if (_isLeaf) then "Table" else "Database",
View = (state) => Table.View(null, [
GetType = () =>
let
tableType = type table [ Name = text, ItemKind = text, Data = table, ItemName = text, IsLeaf = logical ],
withKey = Type.AddTableKey(tableType, {"Name"}, true)
in
withKey meta
[
NavigationTable.NameColumn = "Name",
NavigationTable.DataColumn = "Data",
NavigationTable.ItemKindColumn = "ItemKind",
Preview.DelayColumn = "ItemName",
NavigationTable.IsLeafColumn = "IsLeaf"
],
GetRows = () => if (state <> null) then state else
let
list = listFunction(),
withName = Table.FromRecords(list, {"Name", "ItemKind", "Parameters"}, MissingField.UseNull),
withData = Table.AddColumn(withName, "Data", each dataFunction([Name], [Parameters])),
withItemName = Table.AddColumn(withData, "ItemName", each if [Parameters] = null or Record.FieldCount([Parameters]) = 0 then "Table" else null),
withoutParameters = Table.RemoveColumns(withItemName, {"Parameters"}),
withIsLeaf = Table.AddColumn(withoutParameters, "IsLeaf", each isLeaf),
navTable = Table.ToNavigationTable(withIsLeaf, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
in
navTable,
OnSelectRows = (selector) =>
let
condition = RowExpression.From(selector),
kind = condition[Kind],
leftKind = condition[Left][Kind],
member = condition[Left][MemberName],
value = condition[Right][Value]
in
if (kind = "Binary" and leftKind = "FieldAccess" and member = "Name") then
Table.FromRecords({[
Name = value,
Data = dataFunction(value),
ItemKind = "Table"
ItemName = value,
IsLeaf = true
]})
else
...
])
in
View(null),
artemus What the difference between?
dataFunction(value) and dataFunction([Name], [Parameters])
Oh sorry, ignore everything with [Parameters] I was using that for letting users select functions.