PBIDS File connect to Databrick cannot use query property

[loujiang]

Hello, Dear Power BI community, 

I am using the PBIDS File to make the connection to Databricks easier. I would also use some native query to make the connection more accurate and fit the need. 

But when I using the query to input my designed qurey, there is one error comming, as property query not supported. 

Is there some posibility to input the native query there?

best wishes

loujiang

[BhavinVyas3003 · Accepted solution]

If you need to apply a native SQL query, you can use Power BI Desktop by connecting via PBIDS and then entering your SQL query manually in Power Query’s Advanced Editor.

For automation, you can generate .PBIX files dynamically using PowerShell, Python (pbi-tools), or the Power BI REST API by extracting a PBIX template, modifying the M script to inject your SQL query, and repackaging the file before deploying it to the Power BI Service.

If you are using Power BI Premium, you can modify datasets programmatically using the XMLA Endpoint through SSMS or Tabular Editor, and automate query updates and dataset refreshes using Power Automate or REST API.  

[BhavinVyas3003]

Hi,

PBIDS files are designed only to store connection information, such as: Data source type (Databricks, SQL Server, etc.), Server and database details, Authentication method

They do not support embedding queries because PBIDS files are meant to guide users to the right connection settings, not predefine dataset transformations or filters.

Check if following approach works for you,

Use PBIDS for the connection, then apply the query in Power BI

1. Open Power BI after using PBIDS.
2. Navigate to Transform Data (Power Query Editor).
3. Apply your native SQL query there.

Thanks,

Bhavin

[loujiang]

Hello, Bhavin, 

Thanks for the confirm. Is there some other automated method available to enable the native query? We would generate the sql query automatically and would like to generate Power BI Files with the connection and SQL applied to .