cancel
Showing results for 
Search instead for 
Did you mean: 

Dataflow refresh error Date/Datetime

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

 

 

Status: New
Comments
EMaguire
Regular Visitor

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
Regular Visitor

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
Frequent Visitor

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

msarmento
Frequent Visitor

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.

 

msarmento_0-1623950397440.png

 

mikesmith_bp
Frequent Visitor

@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
Frequent Visitor

@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
Community Support

@Banzai 

 

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

CRI 246210422

 

john_ach
Frequent Visitor

@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
Frequent Visitor

@v-chuncz-msft 

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

Capture.PNG

EMaguire
Regular Visitor

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