I would like to know how I can show a dropdown whilst using Key Authentication

[mjandora]

I am currently using Key Authentication and then providing a URL and then a Key which is working and showing me the data after the connector authenticates. However, instead of manually typing the URL each time, I'd like to show the user a list of 2 or more options in a dropdown. Once that chosen, then present the key field to paste or type in the Key value and authenticate to the data. I'm stuck on trying to pass a dropdown or some sort of selection for the user to choose from. Ideally 2 or more options can be shown to choose from. Code example below:

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

section microServiceDataConnectorURL;
[DataSource.Kind="microServiceDataConnectorURL", Publish="microServiceDataConnectorURL.Publish"]
shared microServiceDataConnectorURL.Contents = (_url as text) =>
let
apiKey = Extension.CurrentCredential()[Key],
headers = [
#"ApiKey" = apiKey,
Accept = "application/vnd.api+json",
#"Content-Type" = "application/json"
],
microService= Web.Contents(_url, [ Headers = headers, ManualCredentials = true ]),
microServiceRecords = Json.Document(microService),
mcrosServiceColumn = Table.FromRecords(microServiceRecords),
microServiceExpanded = Table.ExpandRecordColumn(microService, "levels", {"l0", "l1", "l2", "l3", "l4", "l5", "l6", "l7"})
in
microServiceExpanded;
//[DataSource.Kind="microServiceDataConnectorURL", Publish="microServiceDataConnectorURL.Publish"]

shared ChooseURL = (myChoice as text) as text =>
let
//declare custom number types with metadata for parameters
MyFirstNumberParamType = myChoice
meta
[Documentation.Description = "Please choose an Environment",
Documentation.AllowedValues = {"first Environment choice",
"second Environment choice"}],
//declare custom function type using custom number types
MyFunctionType = type function( _url as MyFirstNumberParamType ) as text,
//cast original function to be of new custom function type
myChoice = Value.ReplaceType(ChooseURL, MyFunctionType)
in
myChoice;

[Stachu]

I think it's only possible if you use dektop files and not in the service

you can set up the drop downs using Query Parameters and templates
https://docs.microsoft.com/en-us/power-query/power-query-query-parameters

https://docs.microsoft.com/en-us/power-bi/desktop-templates

The template has no data so it asks the user to enter the parameter and then starts a refresh - when running locally

in the service the parameters etc. need to be preloaded, so they are basically constant. You could set it up in a way where model has both data sources loaded and you only show 1 of them depending on the user selection, something similar could also work with direct query, but you still need to download the data for both sources.

[mjandora · Accepted solution]

Hi Stachu,

I more or less changed the connector. Basically now, we're storing in files, the base URL for each PROD, QA, and INT environments. We add that to the reminaed of the api call and then based on the user input of environment and key, execute the call against said environment. It came down to an if statement before the api key is called.

section Connector;

v1 = "v1/api-service/api?filter=1460";

INT = LoadFromResource("INTenv") & v1;
QA = LoadFromResource("QAenv") & v1;
PROD = LoadFromResource("PRODenv") & v1;

[DataSource.Kind="Connector", Publish="Connector.Publish"]
shared Connector.Contents = (GetEnvID as text) =>
let
URL = if GetEnvID = "PROD"
then PROD
else if GetEnvID = "QA"
then QA
else INT,
apiKey = Extension.CurrentCredential()[Key],
headers = [
#"ApiKey" = apiKey,
Accept = "application/vnd.api+json",
#"Content-Type" = "application/json"
],
api = Web.Contents(URL, [ Headers = headers, ManualCredentials = true ]),
apiRecords = Json.Document(api),
apiTable = Table.FromRecords(apiRecords),
apiLevelsExpanded = Table.ExpandRecordColumn(apiTable, "levels", {"l0", "l1", "l2", "l3", "l4", "l5", "l6", "l7"})
in
apiLevelsExpanded;

LoadFromResource = (name as text) as text =>
try
Text.FromBinary(Extension.Contents(name))
otherwise
error Error.Unexpected("Resource not found '" & name & "' Does the file exist in your project, and is its Build Action set
to 'Compile'?");

// Data Source Kind description
Connector = [
TestConnection = (dataSourcePath) => { "Connector.Contents" },
Authentication = [
Key = []
],
Label = Extension.LoadString("DataSourceLabel")
];