databricks connectors - dataflow-databricks.query() m language. stopped working

Hi pedro4lmeida05 ,

Sorry to hear you’re running into this issue. Since the method was working previously and suddenly stopped, here’s a step-by-step troubleshooting guide that addresses both recent platform changes and common root causes for the "Dataflow-Databricks.Query()" failure with the error:
Expression.Error: The provided options are not valid.

1. Double-check your credentials and tokens

• If you are using a Personal Access Token (PAT) for Databricks, make sure it hasn’t expired. PATs often have a limited lifespan. Try generating a new token from your Databricks workspace and update your dataflow credentials with the new token.
• Verify that the authentication method you use in your query and dataflow settings matches what is expected and supported.

2. Permissions

• Ensure that the credentials you’re using have sufficient permissions. Ideally, the user or service principal should have Can Use or Can Manage permissions on both the Databricks cluster and the relevant databases/tables.

3. Review changes in Dataflow Gen2 and connector updates

• If your workspace has migrated to or is using Dataflow Gen2, make sure your dataflow and connector version support Azure Databricks. Some features or behaviors may have changed compared to Gen1.
• Consider recreating your dataflow using the most recent connector options available in Dataflow Gen2.

4. Query options and parameters

• The error message references invalid options. Review your query, especially the options part. Sometimes connector updates or back-end changes introduce stricter validation for parameters.
• Try removing or toggling the EnableAutomaticProxyDiscovery option in your query. For example, set it explicitly to true or false, or remove it to see if the behavior changes.

5. Test the query in Power BI Desktop

• You mentioned the query works in Power BI Desktop. Sometimes, the M engine versions differ between Desktop and Dataflow, leading to subtle incompatibilities. Try simplifying the query or removing optional parameters to see if it runs in the Dataflow web editor.

6. Recent Service Updates

• Microsoft occasionally updates the connectors or underlying services, which can break previously working queries. Review the Azure Databricks connector documentation and Microsoft Fabric release notes for any changes in the last week.

7. Consider alternative approaches

• If you continue to have issues, consider using Azure Data Factory or migrating your flows to Dataflow Gen2, which is being prioritized for new features and compatibility.

Summary checklist:

• Refresh or update your Databricks token
• Verify permissions on Databricks and the target data
• Re-check query options for deprecated or invalid parameters
• Try disabling or editing EnableAutomaticProxyDiscovery
• Test in both Desktop and Service for compatibility
• Review recent documentation and release notes

If you try these and the problem persists, please share the exact M code after your latest changes and specify whether you’re using Dataflow Gen1 or Gen2. That will help us dig deeper!

Let us know how it goes. Happy to help further if you need more targeted troubleshooting.

Best,
Burak