Dataflow refresh error Date/Datetime

[Banzai]

Started getting this error on an old dataflow as of 14-Jun-2021. No changes made.

Error: Expression.Error: We cannot apply operator < to types Date and DateTime.

Operator = <

Left = 5/1/2020 12:00:00 AM

Right = 9/30/2020 11:59:59 PM

[EMaguire]

Applying a 'function wrapper' - ie. DateTime.Date( ) or Date.From( ) - around the date fields in a query step wasn't entirely successful for me.

I have now tried disabling the enhanced compute engine on the dataflows. This seems to resolve the problem/errors with the date calculations, but it also means that the data refresh is taking significantly longer - more than an hour for something that previously took 5 minutes.

None of the workarounds here is practical or sustainable in the medium-long term. As mikesmith_bp says - please help Microsoft! 

[steveplatz2]

I performed some additional testing this morning, all with enhanced compute turned on. Here's what I found:

1. Manually create a table and do date comparisons - works

2. Manually create a table and do date comparisons + add a linked table - works

3. Manually create a table and do date comparisons + add a linked table and join it to the manually created table - works

4. Manually create a table and do date comparisons + add a linked table and join it to the manually created table + add another linked table - works

5. Manually create a table and do date comparisons + add a linked table + add another linked table and join the two linked tables - works

6. Add a linked table, join another linked table to it, do a date comparison on the table - works

7. Add a linked table, join another linked table to it, use the result as the source of a third table (computed table) and do the date filter on the third table - fails

    - this is essentially the same as #6 except the date comparison happens in another table

8. Manually create a table use the result as the source of a second table (computed table) and do the date filter on the computed table - fails

From my testing, the failure seems to occur in computed tables and only when using enhanced compute. With enhanced compute turned off, things work as expected.

[InsightBob]

Wow! Pretty robust troubleshooting.  I can only hope Microsoft Engineers are doing the same

[msarmento]

Wrapping all columns with a DateTime.Date funcion did the trick for me.

each DateTime.Date([DataEmissao]) >= DateTime.Date(#date(2020, 12, 1)) or [DataEmissao] = null and DateTime.Date([createdon]) >= DateTime.Date(#date(2020, 12, 1))

EDIT:

Forget to mention, for me it started, out of the blue, on the 6/12.

My company is in Brazil, but our Power BI tenant is in South Central US.

[mikesmith_bp]

@msarmento did you have to make this change throughout your entire dataflow?

My dataflow has ~1000 lines of code in ~50 queries.  This 'workaround' isn't feasibile for me.  Why doesn't Microsoft 'roll back' whatever change they made that is causing this?

[msarmento]

@mikesmith_bp think I'm in luck!

I have only one dataflow that presented this issue and this dataflow has only one step that filters on date columns.

[v-chuncz-msft]

@Banzai 

A similar issue has been reported internally, so stay tuned.

CRI 246210422

[john_ach]

@mikesmith_bp Yes, I went through and modified every date comparison to get my reports up and working again as quickly as possible - took 2 days to figure out the issue and then get everything stable again, but no anticipated fix date from Microsoft yet...

[InsightBob]

@v-chuncz-msft 

Microsoft need to acknowledge the problem here - Power BI is not 'running smoothly'

[EMaguire]

Disappointing there seems to be no acknowledgement/resolution from Microsoft on this issue.

I managed to overcome the problem by switching off the enhanced compute engine on the affected dataflows. 

However at least in one case this introduced a new/separate bug relating to treatment of null values in text fields, where the null values are inexplicably converted and stored as empty rather than null.

Power BI Dataflow with column type text having nul... - Microsoft Power BI Community