Replace Direct query table connection to a Direct query, QUERY connection - Databricks

[lauriemclolo]

I recency was asked to change all my databricks import tables to direct query.  I did that using the TMDL option (which wasn't awesome).

I want to keep the direct query connection, but I want to switch from it pointing to a table to pointing to a query I can edit at will.  How can I do that?  Here is my current connection.  Thank you in advance for your help.

[v-dineshya · Accepted solution]

Hi @lauriemclolo ,

Please refer below complete sample M code. You can change the below code based on your code.

let

Source = Databricks.Multicloud.Catalogs("workspace_url", [Catalog=null, EnableAutomaticProxyDiscovery=null]),
Connection = Source{[Name="catalog_name"]}[Data],
Database = Connection{[Name="database_name",Kind="Database"]}[Data],
NativeQueryResult = Value.NativeQuery(Database, "
SELECT col1, col2
FROM table
WHERE col1 = 'value'
", null, [EnableFolding=true])

in
NativeQueryResult

Note: Change the "workspace_url" with your actual workspace url, "catalog_name" and "database_name" with your catalog_name and database_name, "null" parameter can hold query parameters.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @lauriemclolo ,

Thank you for reaching out to the Microsoft Community Forum.

Power BI allows writing SQL queries directly in M code by using Value.NativeQuery.

Please refer below sample M code.

NativeQueryResult = Value.NativeQuery(Database, "
SELECT col1, col2
FROM table
WHERE col1 = 'value'
", null, [EnableFolding=true])

in
NativeQueryResult

Note:  You can add above M code in your existing code.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[lauriemclolo]

I don't understand.  Where would I place this in my code?

[v-dineshya · Accepted solution]

Hi @lauriemclolo ,

Please refer below complete sample M code. You can change the below code based on your code.

let

Source = Databricks.Multicloud.Catalogs("workspace_url", [Catalog=null, EnableAutomaticProxyDiscovery=null]),
Connection = Source{[Name="catalog_name"]}[Data],
Database = Connection{[Name="database_name",Kind="Database"]}[Data],
NativeQueryResult = Value.NativeQuery(Database, "
SELECT col1, col2
FROM table
WHERE col1 = 'value'
", null, [EnableFolding=true])

in
NativeQueryResult

Note: Change the "workspace_url" with your actual workspace url, "catalog_name" and "database_name" with your catalog_name and database_name, "null" parameter can hold query parameters.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[lauriemclolo · Accepted solution]

Slight adjustment but this was my final connection string

[v-dineshya]

Hi @lauriemclolo ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[danextian]

Your script would be something like below.  Any steps after using the custom query breaks query folding. 

[Aburar_123]

Hi @lauriemclolo ,

you can follow the below step,

connect databricks by writing query then copy its Power query script from Advance editor and replace the existing table (that is connecting the table directly) power query with the copied script.

[lauriemclolo]

I don't understand.  I've already imorted all the data, then changed to direct query using TMDL edits.  There is no native query option when doing that.

[jithin-mathew]

Hi @lauriemclolo 

Please try the native database query section while trying to connect using the get data option of databricks connector.

[jithin-mathew]

Hi @lauriemclolo 

Why don't you try the native database query window while connecting to the data source ?

Please note that all complex queries might not work here but simple ones.

Reference: Import data from a database using native database query - Power Query | Microsoft Learn