cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JoseCVM
Microsoft
Microsoft

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.

1 ACCEPTED SOLUTION
artemus
Microsoft
Microsoft

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.

View solution in original post

23 REPLIES 23
artemus
Microsoft
Microsoft

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),

 

 

@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.

What should I call instead? dataFunction([Name])??? what represents that function? @artemus 

Yea, just remove the second parameter from the function.

 

You define the dataFunction, which returns the table result for the id value passed into the function.

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

@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.

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.

What should thatndatafunction return? and what the difference between datafunction([Name]) and datafunction(value) in OnSelectRows? @artemus 

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!