Incremental Refresh Hourly: how to use range start and end?

[wtdpon]

Hi everyone,

I have a dataset I need to refresh with incrementals, since each hour bring approximately 20M registers. The field in the dataset that I want to use for incrementals is 'date' and it's in the format "2023-09-15 00:00:00.000" (comes like that from athena aws).

I have created two parameters in power query: RangeStartSearch and RangeStartEnd, both of them in date/hour format with the values "15/09/2023 00:00:00" and "15/09/2023 01:00:00" respectively. I am editing the query of my source with them like this:

let
Source = Odbc.Query("dsn=Simba Athena", "SELECT *, now() ""Last Refresh"" FROM ""database"".""searches_hourly"" where ""date"" >= cast(" & RangeStartSearches & " as timestamp) and ""date"" < cast(" & RangeEndSearches & " as timestamp) limit 10 ")
in
Source

This is giving me the following error:

Expression.Error: No se puede aplicar el operador & a los tipos Text y DateTime.
Detalles:
Operator=&
Left=SELECT *, now() "Last Refresh" FROM "database"."searches_hourly" where "date" >= cast(
Right=15/09/2023 0:00:00

Can anyone help? I have used this method and it has worked when only using date format, but now trying to use date/hour I really don't know what else to try.

Thanks in advance!

[123abc]

I Think the error you're encountering is due to a mismatch in data types. You are trying to concatenate a date/time value (RangeStartSearches) with a text string in your query, which Power Query doesn't allow by default. To fix this issue, you need to convert the date/time parameter values to text before concatenating them into your query string. Here's how you can modify your query to achieve this:

```M
let
// Convert date/time parameters to text
RangeStartSearchesText = Text.From(RangeStartSearches),
RangeEndSearchesText = Text.From(RangeEndSearches),

// Use the text values in your query
Source = Odbc.Query("dsn=Simba Athena",
"SELECT *, now() ""Last Refresh"" FROM ""database"".""searches_hourly""
where ""date"" >= cast(" & RangeStartSearchesText & " as timestamp)
and ""date"" < cast(" & RangeEndSearchesText & " as timestamp) limit 10 ")
in
Source
```

By converting the date/time parameters (RangeStartSearches and RangeEndSearches) to text using the `Text.From` function, you ensure that their values are treated as text strings in your query, allowing you to concatenate them without encountering a data type mismatch error.

[wtdpon]

Thanks for your reply!
I have done this by creating to new consults with the text form of the parameters but is still not working. Altough now the error message is different:

DataSource.Error: ODBC: ERROR [HY000] [Simba][Athena] (1040) An error has been thrown from the AWS Athena client. Athena Error No: 130, HTTP Response Code: 400, Exception Name: InvalidRequestException, Error Message: line 1:114: mismatched input '12'. Expecting: '%', '*', '+', '-', '.', '/', 'AND', 'AS', 'AT', 'EXCEPT', 'FETCH', 'GROUP', 'HAVING', 'INTERSECT', 'LIMIT', 'OFFSET', 'OR', 'ORDER', 'UNION', 'WINDOW', '[', '||', <EOF>, <predicate> [Execution ID: ]
Detalles:
DataSourceKind=Odbc
DataSourcePath=dsn=Simba Athena
OdbcErrors=[Table]

Edit: I've realized this is due to missing quotation in the where statements, when fixing that, this is the error I'm having now:

DataSource.Error: ODBC: ERROR [HY000] [Simba][Athena] (1040) An error has been thrown from the AWS Athena client. Error Message: COLUMN_NOT_FOUND: line 1:104: Column ' 9/15/2023 12:00:00 am ' cannot be resolved or requester is not authorized to access requested resources [Execution ID: f2c169d4-ac33-4ecb-894a-7356117471d4]
Detalles:
    DataSourceKind=Odbc
    DataSourcePath=dsn=Simba Athena
    OdbcErrors=[Table]