Custom data connector (SDK) help required

[PowerBISDK]

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

[Anonymous]

any solutions for this? tried existing, not working.

[Anonymous]

any solutions for the issue so far?

[cwebb]

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

https://blog.crossjoin.co.uk/2014/12/02/specifying-allowed-values-sample-values-and-descriptions-for...

Chris

[PowerBISDK]

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.

[cwebb]

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

[PowerBISDK]

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,

[Anuj6]

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

[Jeanesh]

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/