DirectQuery mode errors from Power BI

[digitalnelson]

I keep getting "This step results in a query that is not supported in DirectQuery mode." from Power BI using the spark connector to our Databricks cluster.

• I've tried both standard and high concurrency clusters.
• The standard cluster is using db runtime 6.5 with spark 2.4.5 and scala 2.11.
• Running with 1 driver and 1 worker both DS3_v2.
• Cosmosdb spark is installed via maven with the coordinate of com.microsoft.azure:azure-cosmosdb-spark_2.4.0_2.11:3.1.0.

Accessing a small container in cosmosdb that has been pulled into databricks via a create table call: spark.sql("create table appointments using com.microsoft.azure.cosmosdb.spark options (endpoint 'https://*******.documents.azure.com:443/', database '*******', collection '******', masterkey '{0}')".format(cosmosKey)))

Spark sql shows it maps just fine using show tables and regular SELECT * calls via spark.sql work just fine.

The error is generated when I edit the data transformations in Power BI. I am able to see the table just fine and it works fine in import mode. Import mode defeats the purpose of using databricks in our use case.

The guides in the azure docs say DirectQuery is supported so I'm not sure why I'm getting this error. Is this a known issue or am I missing something? Thanks!

[tknandu]

Hi Nelson, could you please share a screenshot of where you observe the message 'This step results in a query that is not supported in DirectQuery mode'? Is this in the Power Query Editor tab?

[digitalnelson]

Sure. Yes, it's in the query editor tab. Even if I get rid of the Navigation step and leave only the Source step, it still shows the error.

[tknandu]

The PBI <> Spark connector leverages ODBC driver under the hood - could you please turn on Diagnostics to collect ODBC warnings in the traces that can help validate if an issue exists