Forum Discussion
Custom Connector and Navigation Tables
Hello,
I'm new to powerquery and dataconnectors, and I'm having trouble implementing the user flow I designed. Basically, it should go like this:
Authentication: KEY
TableName: FOO
It hits back to the database, and checks if tableFOO exists. If it does, it returns the data as normal. If it doesnt, it should direct the user to a navigation table of all available tables. This is where I'm having trouble. Once at the Navigation Table, things should look like this:
KEY
| TABLE1
| TABLE2
...
| TABLEN
And upon selecting a table, it should show only the table's schema with dummy data (So I dont retrieve all the data for all the tables). The problem is, all I seem to be able to do with the NavTable is load up the raw data. Once I click load, it tries to actually load the selected item. Is it possible to redirect this to another direct query? If so, how do I go about it, and are there any good examples of how this works? I suspect that this example goes in the direction I want, but I'm not sure.
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.
23 Replies
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.
Is there more in-depth documentation for the Table.View usage (Or for powerquery and connector development in general)? I've read about it, but most of it is very superficial and I'm having trouble understanding what is the state, how do I make my Navigation Table to also be a table view etc.
Also, am I correct in thinking that the OnSelectRows handler will override the behaviour of the "Load" button when I select one of them to load?
JoseCVM wrote:Is there more in-depth documentation for the Table.View usage (Or for powerquery and connector development in general)? I've read about it, but most of it is very superficial and I'm having trouble understanding what is the state, how do I make my Navigation Table to also be a table view etc.
Also, am I correct in thinking that the OnSelectRows handler will override the behaviour of the "Load" button when I select one of them to load?
As far as loading goes. It will override that and potentially further operations past the load button. E.g. you could program it in such that the user loads the table, then filters it, adds a column and does a group by operation to all be folded into a single request to the server, which would ask the server to aggregate the data instead of downloading it all and then aggregating it.
I'm not sure where the definative documentation is for all of this logic. I have access to the source code, so I haven't needed to look for the public documentation on this.
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),
