Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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

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 ...
Need Help
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    Hi Anonymous ,

    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

  • Anonymous's avatar
    Anonymous
    1 year ago

    Slight adjustment but this was my final connection string

     

v-dineshya's avatar
v-dineshya
Icon for Community Support rankCommunity Support
1 year ago

Hi Anonymous ,

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

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

    • v-dineshya's avatar
      v-dineshya
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi Anonymous ,

      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's avatar
        v-dineshya
        Icon for Community Support rankCommunity Support
        1 year ago

        Hi Anonymous ,

        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