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.
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
artemus How to understand, which values are selected for rows filter? for example by which id to filter?
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
]})The code basicly says:
OnSelectRows = When this table is filtered, apply the following logic. This logic only handles: Table.SelectRows(table, each [Name] = "UserEnteredValue"), which is the extended form of table{[Name = "UserEnteredValue]}
condition = Get Abstract syntax tree for user's filter function
kind = Get the kind of the top level operation (this is expected to be "Binary", which includes all binary operations. The one we are interested in is equals ( = ))
leftKind : We expect this to be in the form of [Name], which is the same as _[Name].
member: This is what inside the []. In this case "Name"
value: This is the string the user is comparing with.
In general, Just run: = RowExpression.From(each [Name] = "MyValue") and see what it returns. Then make your code check and match the output of that, and get the "MyValue" string.
How to understand which table is calling OnSelectRows? artemus
The navigation table. You get this table by copying the Table.NavigationTable from the link I posted origionally. This is the same type of table you will see if you open a connection to SQL, and remove all the steps except the first one.
artemus Yes, but inside OnSelectRows how you access table? please write
Table.SelectRows(Persons, each ([PersonId] = "02b67c20-1619-4943-adf0-c1951308a9b0")) and Table.SelectRows(Products, each ([PersonId] = "02b67c20-1619-4943-adf0-c1951308a9b0")), when this filter happenes how to understand which table performe it Persons or Products? How distinguish which datafunction call, for Persons or Products? artemus
Just add a parameter to the top level function to track it.
artemus which one? Could you said concrete place?
Thanks
For example, you can add it like:
NavigationTableFromList = (category as text, dataFunction as function, listFunction as function, optional isLeaf as logical) as table =>Then when you cann NavigationTableFromList, just pass in the category.
artemus How to pass state from OnSelectRow? I get always state=null.
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 ]})
Table.SelectRows(#"PeoplesTable", each ([PersonId] = "188997-d485-47d4-b721-515194303fa3" or [PersonId] = "565950f29-ab8a-4b6e-ad0f-de67d0bf32
6b5") and ([FirstName] = "John))artemus if there is more then one value, how to parse them? For example above sample, how to understand what were filtered?
Make a simple query in power bi Desktop with (no need to have any data loaded):
RowExpression.From( each ([PersonId] = "188997-d485-47d4-b721-515194303fa3" or [PersonId] = "565950f29-ab8a-4b6e-ad0f-de67d0bf32
6b5") and ([FirstName] = "John"))Then drilldown to find what the strucutre is. and detect that pattern in your code.