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

@InsightBob Did Microsoft give any indication on when they think the issue may be resolved?

InsightBob
Frequent Visitor

No indication they're still investigating. We've sent them boats loads of logs and scripts for them to troubleshoot.

 

To be honest I'm having trouble getting MS to acknowledge a problem on their side here, despite others having the same symptoms at the same time all of a sudden after service degradation on their US EAST 2 cluster.

 

This is really bugging me because we can't find/recreate the error in query editor, either in the service or desktop.

 

The only workaround we've identified is to disable load of linked entities in downstream dataflows, but this isn't palatable for me long term.

 

Will report back what the MS engineers come up with.

john_ach
Frequent Visitor

Cross-link to my thread (thanks for spotting it InsightBob), with a workaround which got me back up and running while Microsoft investigate.
https://community.powerbi.com/t5/Service/Incorrect-Errors-in-Appearing-in-Dataflows/m-p/1901427#M131...

InsightBob
Frequent Visitor

@john_ach your solution does work.  

Our dataflow structure is very similar to yours (Ingest > Cleanse > Fact/Dimension).  It seems something is being lost in translation between linked entities.

InsightBob
Frequent Visitor

Switching off enhanced compute engine for affected dataflows also resolves - that may be root cause

silvajf
Frequent Visitor

I have the same problem!

 

I have solved some of them by just re-assigning “Date format” before each operation and it works, but the columns were already date formatted, and doing this for each table and before each operation takes a long time.

 

Has someone solved this in another way?

 

Many thanks!

john_ach
Frequent Visitor

The workaround for me was to cast the (date type) field to a date type in the filter logic, for example:


From 

[Record Date] > DateTime.Date(DateTime.LocalNow())


to

DateTime.Date([Record Date]) > DateTime.Date(DateTime.LocalNow())

 

I'm using Direct Query to my dataflows, so turning off Enhanced Compute Engine won't work for me. Though that's not working as expected currently...

steveplatz2
Regular Visitor

Interesting. I tried something similar:

 

Date.From([first date]) < Date.From([secoind date])

 

and still had the comparison issue. Seems the DateTime may be the critical component with this.

Banzai
Advocate I

Yep, similar experiences.

 

Any function wrapper run may succeed once on demand, but the next (scheduled) one fails. 

mikesmith_bp
Frequent Visitor

This is not the first time Date fields have suddenly started crashing my dataflow.  Please help Microsoft!