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

[steveplatz2]

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

[InsightBob]

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]

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]

@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]

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

[Anonymous]

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]

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]

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]

Yep, similar experiences.

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

[mikesmith_bp]

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