Prompting users for inputs

[fabiogbe]

Hi all, wondering if it is possible to capture external parameters based on user's inputs. For instance, let's say we want to enable the user to filter results only for year = 2024, and this would be passed to the composition of the URL (&year=2024). Is that possible?

[geologyrocks]

How is the user interacting with the connector? Via Power BI Desktop / Service? The way we do it is using an optional parameter on the Connector.Contents("function name", optional "functionParams") entrance method.

And then how is Contents() passing through to your Web.Contents() method? That part is easy enough.

The laptop's off now, but I'm happy to share relevant (generic) snippets tomorrow if necessary!

[fabiogbe]

thanks for the reply! the connector should work both in Power BI Desktop and Service (once Gateway is properly set-up). I understand the suggested approach of using optional parameters in entrance method. I wonder if this solution is compatible to (i) using a navigation table function in the shared function, and (ii) using credentials parameters that will be used in the authentication functions.

[geologyrocks]

a navigation table function

I haven't tried these yet, what do you mean? Can you leave a code snippet or pseudocode comment please?

using credentials parameters that will be used in the authentication functions

We use Azure authentication with SSO so don't have this problem, but we do have a mandatory environment parameter that gets passed in so the user can pick prod or training environments. So I think you'll be fine.

[fabiogbe]

thanks again for the responses!

This is how I'm currently using the shared function:

[DataSource.Kind="CustomConnector", Publish="CustomConnector.Publish"]
shared CustomConnector.Contents = Value.ReplaceType(NavTable, type function () as any);

It points to a nested Navigation table:

NavTable = () as table =>

let
    #"OrgTable" = GetConnections(),
    AddData = Table.AddColumn(#"OrgTable", "Data", each NavTableLeaf([tenantId])),
    AddItemKind = Table.AddColumn(AddData, "ItemKind", each "Table", type text),
    AddItemName = Table.AddColumn(AddItemKind, "ItemName", each "Table", type text),
    AddIsLeaf = Table.AddColumn(AddItemName, "IsLeaf", each false, type logical),
    NavTable = Table.ToNavigationTable(AddIsLeaf, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
in
    NavTable;

//Navigation Table structure - leaf
NavTableLeaf = (tenantId as text) as table =>

let
    source = #table({"Name","Data","ItemKind","ItemName","IsLeaf"},{
        {"Accounts",Accounts(tenantId),"Table","Table",true},
        {"Journals",Journals(tenantId),"Table","Table",true},
        {"Users",Users(tenantId),"Table","Table",true},
        {"Invoices",Invoices(tenantId),"Table","Table",true}
               
    }),

    navtable = Table.ToNavigationTable(source,{"Name"},"Name","Data","ItemKind","ItemName","IsLeaf")

in
    navtable;

So the challenge is to conciliate the utilization of the navigation tables, and also prompting the user for inputs!

[geologyrocks]

No sweat! I see. Our connector takes a static parameter, settable when you reference the contents method in a Power BI "blank query" / power query .query.pq file.

I've just had a look at the Power Query docs for Navigation Tables and Connect to data, and this is not something I've used before, not at all! It seems interesting though as a way of selecting what data you want returned.

Aligning your example to my world view / area of understanding, it would look like

shared CustomConnector.Contents = (optional userParams as text) => 
  if userParams = null or userParams = ""
  then NavTable()
  else 
    let
      checkedParams = CheckUserParams(userParams), // e.g. check for unsafe or unpermitted query format
    in
      NavTable(checkedParams);

CheckUserParams = (userParams as text) => "year=2024"; // placeholder!

NavTable = (optional userParams) as table =>
let
    #"OrgTable" = GetConnections(userParams),
    AddData = Table.AddColumn(#"OrgTable", "Data", each NavTableLeaf([tenantId])),
    AddItemKind = Table.AddColumn(AddData, "ItemKind", each "Table", type text),
    AddItemName = Table.AddColumn(AddItemKind, "ItemName", each "Table", type text),
    AddIsLeaf = Table.AddColumn(AddItemName, "IsLeaf", each false, type logical),
    NavTable = Table.ToNavigationTable(AddIsLeaf, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
in
    NavTable;

//Navigation Table structure - leaf
NavTableLeaf = (tenantId as text) as table =>
let
    source = #table({"Name","Data","ItemKind","ItemName","IsLeaf"},{
        {"Accounts",Accounts(tenantId),"Table","Table",true},
        {"Journals",Journals(tenantId),"Table","Table",true},
        {"Users",Users(tenantId),"Table","Table",true},
        {"Invoices",Invoices(tenantId),"Table","Table",true}
               
    }),
    navtable = Table.ToNavigationTable(source,{"Name"},"Name","Data","ItemKind","ItemName","IsLeaf")
in
    navtable;

[fabiogbe]

hey geologyrocks, hope all is well on your end. Your structure indeed worked well, and I'm able to both capture the users inputs as well as use the navigation table. So thanks for that!

My final challenge is to use the user inputs in the authentication function, which is a separate function in the connector.pq file. The authentication functions in the custom connector sdk are independent, so I'm trying to figure out how to call the input variables later in the game.