DataFlow Gen2 issue We couldn't convert to Date.

[SnoekL]

I'm loading data from a on-premise Gupta database using ODBC with DataFlow Gen2

It's working fine until I try to make it an incremental refresh using the last date from the already loaded table.

I'm extracting the data from the warehouse with:

This seems to be working fine.

I'm using a native query and I use the output of the first query as a value (VW_INCR_DATE) in the sql native query

In the dataflow (when editing) it seems to be working. The results are showing correct. 

But the issue shows up when the dataflow runs.

I don't understand this issue. Is anyone able to help me with this issue?

[SnoekL · Accepted solution]

The VW_INCR_DATE value is coming from the warehouse and is the last date for which the data is already loaded. That is not from the source.

[SnoekL · Accepted solution]

I found the solution. The query which extracts the Date value from the warehouse should have "enable staging" false to get it working. Not sure why though :).

[aj1973]

Hi @SnoekL 

Dosen't look like implemented refresh is implemented the right way. Please refer to this

https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

[SnoekL]

aj1973, the topic you are referring to is actually the one I used to build my incremental refresh.

Big difference though is that the example has sql database as a source where I'm connecting via odbc to a local database. 

And the example has a datekey in the source which is a integer where my source only has a datetime field to use to get the incremental data.

I tried to convert the source data to different datatypes but than I get different errors as well.  So i'm stuck at the moment.

[aj1973]

When connecting to ODBC

Add a SQL statement to convert/cast this part 

to Date before loading into Power Query Online.

Power Query couldn't accept to add a step to Format that column so try with SQL statement, it should work.

[SnoekL · Accepted solution]

The VW_INCR_DATE value is coming from the warehouse and is the last date for which the data is already loaded. That is not from the source.