Direct Query Mode -> Azure Databricks -> Creates empty dummy databricks connections (stops refresh)

[Krishnakumar_C]

The below is my Power Query that connects to multiple databricks clusters through slicer selections via ManagedParameters.
SourceName and HttpName are variables that gets set dynamically based on the CountryCode variable set via ManagedParameter "countryParameter".

let
CountryCode = #"countryParameter",    // ManagedParameter

SchemaName =if CountryCode = "Canada" then "CanadaDB"
else if CountryCode = "Australia" then "AusDB"
else error "Invalid Country selected",

SourceName =if CountryCode = "Canada" then "adb-12345.azuredatabricks.net"
else if CountryCode = "Australia" then "adb-6789.azuredatabricks.net"
else error "adb-12345.azuredatabricks.net",

HttpName =if CountryCode = "Canada" then "sql/protocolv1/o/12345-qocjv3t1"
else if CountryCode = "Australia" then "sql/protocolv1/o/6789-jlwi5hdb"
else error "sql/protocolv1/o/12345-qocjv3t1",

Source = Value.NativeQuery(Databricks.Catalogs(SourceName, HttpName, [Catalog="hive_metastore", Database=null, EnableAutomaticProxyDiscovery=null]){[Name="hive_metastore",Kind="Database"]}[Data], "select code, #(lf) item, #(lf) type_desc, #(lf) unit_desc, #(lf) price, #(lf)product,#(lf) #(lf) quantity from " & SchemaName & ".tableA", null, [EnableFolding=true])
in
Source

This works fine. However, when i publish to the workspace, i see an empty databricks under Datasource credentials for which i dont know what key or credentials i have to enter. This is preventing me from refresh. 

[Anonymous]

Hi  @Krishnakumar_C ,

Based on the Power Query screenshot you provided on the desktop, it appears that you’ve used a ‘country’ parameter as part of the connection string to link to a data source. 

After publishing the dataset, you should also see this ‘country’ parameter value in the dataset settings and verify whether it corresponds to the data source you intend to connect to.

Edit parameter settings in the Power BI service - Power BI | Microsoft Learn

Regarding the credentials you’ve entered for authentication, make sure to confirm the type and format of credentials your Power BI desktop is using to connect to this data source. Consider using the same authentication type and credentials to connect to the data source.  If you’re using a gateway, ensure that the gateway is within the same local domain as your Power BI desktop.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Krishnakumar_C]

Hi Liu, Thanks for replying.
The "country" managedParameter has a default value of Canada. However, its dynamically set at run time by the users through "CountryTable" slicer setting. The "country" column in "CountryTable" has been binded to the "countryParameter" managed Parameter as show in the given screenshot.

So if you see the below screenshot, the slicer enabled user to set "Canada" or "Australia". 
Since the slicer is bound to the countryParameter, as per the "if" condition in the Power query in Direct Query mode, it dynamically switches to different databricks.
////// snippet from power query

CountryCode = #"countryParameter",    // ManagedParameter

SourceName =if CountryCode = "Canada" then "adb-12345.azuredatabricks.net"
else if CountryCode = "Australia" then "adb-6789.azuredatabricks.net"
else error "adb-12345.azuredatabricks.net",

Source = Value.NativeQuery(Databricks.Catalogs(SourceName, HttpName, [Catalog="hive_metastore", Database=null, EnableAutomaticProxyDiscovery=null]){[Name="hive_metastore",Kind="Database"]}[Data], "select code, #(lf) item, #(lf) type_desc, #(lf) unit_desc, #(lf) price, #(lf)product,#(lf) #(lf) quantity from " & SchemaName & ".tableA", null, [EnableFolding=true])

/////

This dynamic switching of datasources in direct query mode is working fine without any problems.
The expectation is under "Data source credentials" in the workspace, it should prompt credentials for "2" Databricks (Canada, Australia). Instead as shown in my original screenshot, its prompting credentials for an empty databricks where host and httpPath are empty.

extensionDataSourcePath 

{"host":"","httpPath":""}