Not able to find Custom SQL option in Dataflow when source is databricks

[mhmmd_srf]

Hi All,

I am trying to create a dataflow on top of databricks as data source. However, I am not able to find any option to write custom SQL. Though, it is there when I change my source to HANA or SQL Server.

Could you please help here.

Thanks,

Sarif

[Poojara_D12]

Hi @mhmmd_srf 

When creating a dataflow in Power BI using Databricks as the data source, you may notice that the option to write custom SQL queries is not available, unlike with traditional relational sources like SQL Server or SAP HANA. This limitation exists because the Databricks connector in Power BI dataflows currently does not support query folding or direct SQL input within the Power Query Online editor. Instead, it enforces a more GUI-based approach where you must use the standard table and column selection method followed by transformation steps using Power Query (M language).

This can be frustrating if you rely on complex SQL logic or want to optimize performance by pushing query logic down to Databricks. One workaround is to define your transformations as views or custom queries within Databricks itself, and then reference those views from Power BI dataflows. Another alternative is to use a Power BI Desktop file to connect to Databricks, write your custom SQL there, and then export the M script to use inside the dataflow (though this still won’t enable direct SQL execution, it helps preserve logic). Until Microsoft adds support for native SQL editing for Databricks in dataflows, these workarounds are the only viable approaches.

[GilbertQ]

Hi @mhmmd_srf 

It is possible to write a custom sequel query using data bricks. Here is an example below on how to achieve this. 

In the example below, you would need to replace the details with your databricks, plus the details and SQL code.

let
    Source = Databricks.Catalogs("adb-1234567890123456.7.azuredatabricks.net", "/sql/1.0/warehouses/abc123def456", [Catalog="samples", Schema="nyctaxi"]),
    Query = Value.NativeQuery(Source, "SELECT * FROM samples.nyctaxi.trips WHERE tpep_pickup_datetime >= DATEADD(day, -7, CURRENT_DATE)")
in
    Query

[mhmmd_srf]

So, I need to do this in Advanced Editor. No option while creating connection like we have if data source is SQL Server?

Thanks,

MS