Select Multiple Drop Down Navigation Table Menu (for custom data connector)

[Anonymous]

I am building my navigation table for my custom data connector and am looking to have a multi-select option in the menu as seen in one of the examples here: https://github.com/microsoft/DataConnectors/tree/master/samples/OpenApiSample

The closest I have been able to get is a single selectable drop down list, and as far as I can find there is nothing in the documentation on how to do this as there are many functions for custom data connectors which are not documented...

[rsimcoe]

I would also be interested in how to do this if anyone has any helpful tips.

Thanks,

[Anonymous · Accepted solution]

I have actually, after many many hours of trial and error, figured out how to do this. 
It turned out, as most things do, to be annoyingly simple...

When you are adding the meta-data to the function for the navigation table, instead of having it as type text, simply put it as type list instead, and set the allowed values to be whatever you want to appear in the list box. 

Hope this helps people!

[ppraveenk]

Hi @Anonymous  I am struggling for few days now to understand how to add user input parameters for a navigation table. Could you please help to elaborate how you managed to achieve this fucntinality. Any code reference would be greately helpful. Thanks!

[Anonymous]

Hi @ppraveenk , you can build a navigation table like this, and then expose your functions:

Hope it helps.

[ppraveenk]

Hi @Anonymous 

Many thanks for you reply. I want to capture the Start and End Date parameters from User before invoking the RestAPI call. 

After implementing your suggestion, I am not seeing any input fields in the navigator, but getting a message "No parameter values specified". 

Sorry for being ignorant, but I am trying my hands on power bi connector for the very first time.

This is how I am trying. Request you to let me know if  I am doing something incorrectly. Thanks in advance!

let
source = #table({"Name", "Data", "ItemKind", "ItemName", "IsLeaf"}, {
{ "UserSearch", (startDate as text, endDate as text) as table => GetUserSearchTable(startDate, endDate), "Table", "Table", true }
{ "Users", GetUsersTable(), "Table", "Table", true }
}),
navTable = Table.ToNavigationTable(source, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
in
navTable;

// Not sure if these parameters are required
//startDate = DateTime.ToText(DateTime.From(Date.AddDays(Date.From(DateTime.LocalNow()),-30)),"yyyy-MM-dd") meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true];
//endDate = DateTime.ToText(DateTime.From(Date.AddDays(Date.From(DateTime.LocalNow()),-1)),"yyyy-MM-dd") meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true];

GetUserSearchTable = (startDate as text, endDate as text) as table =>
let
body = "{ ""endDate"" : """&endDate&""" , ""startDate"" : """&startDate&"""}",
source = Web.Contents("https://rest.api/users", [ RelativePath = "search", Content = Text.ToBinary(body)]),
json = Json.Document(source),
#"Converted to Table" = Record.ToTable(json),
Value = #"Converted to Table"{0}[Value],
#"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"userID"}, {"userID"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"userID", "Id"}}),
#"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "GetListTable", each GetListTable([Id])),
#"Expanded Users" = Table.ExpandTableColumn(#"Invoked Custom Function", "GetListTable", {"name", "type"}, {"name", "type"})
in
#"Expanded Users";

[Anonymous]

The thing that catches my eye is the "as table" that you have on your function. If it receives parameters, you have to define it as a function, I think.
So remove it from your NavTable and GetUserSearchTable. Also, define your GetUserSearch table as "function" on the navTable.

[ppraveenk]

Hi @Anonymous, thanks for your response. 

Could you please let me know if you are aware of how to pass default value as today' date for Start and End date params in Navigation table. Highly appreciate!

[Anonymous]

Hi all, I haven't worked on this in a while so can't give much more insight than in my above post. To see the code where I used it (or a since updated version) have a look at the intelligent plant industrial app store data connector, that is what I was used this functionality for.

Thanks 

[Anonymous]

Hey @Anonymous, can you explain a bit more of how you did it? 
I'm currently struggling to add a dropdown to my function parameters in the navigator.

I can replicate it in Advanced Editor and works fine, but not in the connector... 

Best Regards, 
Carlos