Calendar Week Delegation Failed at Databricks SQL Warehouse

[loujiang]

Hello, Dear Community,

I am using Power BI Destop Version Version: 2.145.1602.0 64-bit (July 2025) to get data from Databricks Unity catalog delta table with connector Azure Databricks.

I am facing the issue the query folder delegate some not compactable Query to Databricks SQL. It happened at the process I do the transfor to a timestamp column. I would like to convert this timestamp to calendar week. I do this

After that, I click on the close and apply. But got some errors:

It seems like the delegated function week(<Timestamp>) are not supported in the Databricks SQL, instead the weekofyear(<Timestamp>) should be used.

The overall experience is strange for me

1. The Data Preview in Power BI Desktop Power Query Editor works without problem

2. After close and apply, the load of data failed.

3. The week function is not supported but delegated to databricks SQL.

Is this already a known issue? Will it be handled as a bug?

Thanks for your support.

best wishes

loujiang

[audreygerred]

Hi! Can you verify week is valid in your SQL dialect? Have you checked to make sure you have USE privileges on the database where the function lives? Some functions are only available in specific engines. Also, week is technically a dimension of data and facts and dims are supposed to be stored in separate tables, so I get week number from my date table using the relationship on date (following star schema).

[loujiang]

Hello, 

Thanks for the hint. Here is my test on the Databricks SQL: 

But if I change to the weekofyear(), it will work: 

[v-ssriganesh]

Hi @loujiang,
Thank you for reaching out to the Microsoft fabric community forum.

it seems we’re on the right track with weekofyear() working in Databricks SQL, but the issue with Power BI’s query folding hasn’t been fully resolved yet. Please try one of the workarounds suggested (e.g., custom SQL with weekofyear(), disabling query folding with Table.Buffer() or using DAX WEEKNUM()) and let us know if it works. This will help us confirm a solution and assist other members facing similar issues.

Thank you, @audreygerred, for sharing valuable insights. Your suggestion to verify the SQL dialect and check privileges helped clarify the issue. We appreciate your efforts in supporting the community.

Best regards,
Ganesh Singamshetty.

[loujiang]

Hello, 

Thanks for the suggestion. I resolved my issue with native query defined before qurey fording to generate the calander week by myself. 

Would you please inform, if this is view as a bug of Power BI and will it be solved in the upcoming versions?

best wishes

loujiang

[v-ssriganesh · Accepted solution]

Hello @loujiang,

Thank you for the update. I'm glad to hear you've successfully resolved the issue by using a native query to handle the calendar week calculation before query folding kicks in. That's a smart and efficient workaround and it's great that it's working for you now.

Regarding your question: Yes, this appears to be a compatibility issue (or bug) in how Power BI's query folding translates the "Week of Year" transformation to SQL for the Databricks connector, delegating an unsupported week() function instead of the Databricks-compatible weekofyear().

However, there's no specific confirmation yet that this exact week() delegation issue is fixed in upcoming versions. We recommend raising it in the Ideas forum (Fabric Ideas - Microsoft Fabric Community) and upvoting it so the internal team can review and prioritize it for implementation soon.

Thanks again for the resolution update; it'll help others searching for similar issues.

Best regards,
Ganesh Singamshetty

[loujiang]

Thanks for the hint and suggestions!