topic Re: Custom data connector (SDK) help required in Developer

Custom data connector (SDK) help required

PowerBISDK — Sat, 20 Oct 2018 15:16:05 GMT

Dear Forum members,

I am working on a small custom connector, it works fine so far. What I wanted to have it before connecting to the service I would have list of names, once user select this list then I will pass the selected user string to my service as a header value, is this possible?

I want something similar to this

https://github.com/Microsoft/DataConnectors/tree/master/samples/HelloWorldWithDocs

What exactly needed is, I have list of names stored in my table in a database, I pull these names out first and display them as the above forum is showing then when user selects any of the value, I add this to my request header before calling the service. I am scratching my head, it seems possible by looking at the link above but not sure why I am unable to crack it.

Example:

I have to pass database name for variety of databases within SQL server - to do this I need to list all my db names within and then once user selects which database then service will be called which has a header value contains the DB name which then amend the connection string dynamically on the service side which finally pull the data out.

Please if anyone can shed some light, I am really running out of ideas here.

Many thanks in advance

Re: Custom data connector (SDK) help required

cwebb — Fri, 26 Oct 2018 19:24:29 GMT

If I understand you properly, then yes I think it should be possible. You should return a function in your Navigation table and then do something like this to populate the dropdown lists:

https://blog.crossjoin.co.uk/2014/11/27/specifying-allowed-values-sample-values-and-descriptions-for-function-parameters-in-power-querypart-1/

https://blog.crossjoin.co.uk/2014/12/02/specifying-allowed-values-sample-values-and-descriptions-for-function-parameters-in-power-querypart-2/

Chris

Re: Custom data connector (SDK) help required

PowerBISDK — Sat, 27 Oct 2018 12:16:22 GMT

Hi Chris,

Thank you very much, appreciated Your response, i did see that but not sure how I am going to work out, infact the link given below from your website has another link within from one of the responses from the "George Qiaos" to make it dynamic, but his example is within Power BI and uses temporary table within the Power BI.

I am still trying to make sense of your posts which I did try previously but did not get anywhere, any sample with dynamic list would be great if you ahve otherwise I will try and work.

I am trying to work out other solution, if you can throw some light or share some sample would be really great. What I am thinking to do is instead for the user which is logged on - I bring the complete dataset with all the dbs the user has access to list them with the number of tables, kind of nested tables but within each databases - ItemKind database.

Is there anyway I can itterate through each database name which I bring from the database first and for each database I will then call my service and create a navigational view something like this:

DB1
List of tables (probably 4-5 or may be 10 based on restrictions)

DB2

List of tables (probably 4-5 or may be 10 based on restrictions)

DB3

List of tables (probably 4-5 or may be 10 based on restrictions)

In simple, I would like to call my service method to provide me list of databases, which then loop through for each of the DB to do a service call with the DB name then bring the relevant tables with the data and create an hierarchical structure based on the database which it belongs to.

Re: Custom data connector (SDK) help required

cwebb — Sun, 28 Oct 2018 11:41:16 GMT

Are you saying that you want to populate a navigation table with a list of databases and, under each database, a list of tables and have it look something like what is described here in the multi-level navigation table section?

https://docs.microsoft.com/en-us/power-query/handlingnavigationtables

Chris

Re: Custom data connector (SDK) help required

PowerBISDK — Sun, 28 Oct 2018 14:51:45 GMT

Hi Chris,

Thank you very much for your response, Yes, exactly, multi-level or nested navigation, I have tried using the hard-coded sample then changed the code to bring databases dynamically in the name part as below, i have tried to create a sample code for what I required, not sure how to loop through each table to pass its name and get the tables relevant to it:

shared NavigationTable.Nested = (url) as table => let objects

= #table( {"Name", "Key", "Data", "ItemKind", "ItemName", "IsLeaf"},

{ {GetDatabaseName(url), "n1", NavTable(url), "Database", "Database", false}

}), NavTable = Table.ToNavigationTable(objects, {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf") in NavTable;

GetDatabaseName= (url as text) as table =>
let

Source = DataConnector_3.Feed(url & "GetDatabases"),
json = Json.Document(Source,1252),
MyJsonTable= Table.FromRecords(json),
#"Removed Other Columns" = Table.SelectColumns(MyJsonTable,{"DBName"})

in
#"Removed Other Columns";

NavTable = (url as text) as table =>
let
source = #table({"Name", "Data", "ItemKind", "ItemName", "IsLeaf"}, {
{ "Table1", GetTable1(url), "Table", "Table", true}
}),
navTable = Table.ToNavigationTable(source, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")

in
navTable;

GetTable1 = (url as text,dbname as text) as table =>
let
Source = DataConnector.Feed(url & "GetTable1",dbname),
json = Json.Document(Source,1252),
MyJsonTable= Table.FromRecords(json)
in
MyJsonTable;

DataConnector.Feed = (url as text, optional dbname as text) =>
let
Credential = Extension.CurrentCredential(),
split=Text.Split(Credential[Username],"#"),
source = Web.Contents(url, [ Headers = DefaultRequestHeaders, ManualCredentials = true, Query = [
user_key = split, db_name=dbname]]),
json = Json.Document(source)
in
json;

I am not sure, how to itterate the "NavigationTable.Nested" function for each database for which the db name would be pass to CreateNavTable funtion.

I have searched internet and still un-able to find any sample which helps me create dynamic navigation.

Do you know any sample available for dynamic navigation? Would be really helpful.

Kind regards,

Re: Custom data connector (SDK) help required

Anuj6 — Mon, 23 Dec 2019 04:52:53 GMT

Hi,

Were you able to find the solution to this problem, i am also trying to do something like this in my custom connector.

Thanks

Re: Custom data connector (SDK) help required

Jeanesh — Mon, 27 Jan 2020 21:44:14 GMT

Hi,

Rather than build a connector in the power query SDK why not build it outside in the cloud?

My course teaches you exactly how to do this and goes through detailed walkthroughs and everything I do is done without writing any code!

https://businessautomator.co.uk/no-code-business-intelligence-connector-development/

Re: Custom data connector (SDK) help required

Anonymous — Thu, 21 Jan 2021 07:55:59 GMT

any solutions for the issue so far?

Re: Custom data connector (SDK) help required

Anonymous — Thu, 21 Jan 2021 08:02:48 GMT

any solutions for this? tried existing, not working.