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 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.

 

 

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

     

10 Replies

  • jithin-mathew's avatar
    jithin-mathew
    Frequent Visitor
    1 year ago

    Hi Anonymous 

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

     

  • Aburar_123's avatar
    Aburar_123
    Solution Supplier
    1 year ago

    Hi Anonymous ,

    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.

     

  • danextian's avatar
    danextian
    Super User
    1 year ago

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

     

  • v-dineshya's avatar
    v-dineshya
    Community 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
        Community 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