In this video from Guy in a Cube, you can clearly see that the query sent by Power BI to Azure has a convert to datetime2 function - something like this is presumably missing for Athena/Presto, which needs the type constructor TIMESTAMP in order to do datetime comparisons (https://stackoverflow.com/a/38041684/3675679), and of course incremental refresh must be based on datetime fields. I am using the datetime field adv_date for the incremental load.
Here is what the M query looks like in Power Query Editor:
= Table.SelectRows(#"Removed Columns1", each [adv_date] >= RangeStart and [adv_date] < RangeEnd)
And here is the resultant error message in Athena:
Your query has the following errors:SYNTAX_ERROR: line 1:1: Incorrect number of parameters: expected 2 but found 0
Whilst this is how Athena interprets the query:
select "col1", "col2", "adv_date" from "AwsDataCatalog"."test"."test_table" where "adv_date" >= ? and "adv_date" < ?
I have contacted Power BI support without success.
Does anyone have a workaround for this by any chance? Happy to provide more info if needed.