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.
Could you please, in detail write, how to get newState for Restful API call, and how to get from "condition", value of filter, by which rows were selected, for example rows filtered by typeId column.
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),