How to use incremental refresh by forcing queryfolding with a predefined SQL Query and ODBC

[Anonymous]

Hello I am setting up incremental refresh for a  table of mine and since I am providing a SQL statement before applying the parameters the table ends up being imported before it is filtered out by the parameters (the query isn't folded nativelly).

I've found this workaround somewhere thet retrieves the range start and end texts and add them in the query so it sends the where clause with the dinamic ranges (sorry for bad image thats the original form the source i've found and is low res)

So I am trying to apply it to my model, however I do not know how the syntax should be as the script down bellow it returns "column datetime 2 does not exist"

the code provided is 

let
Fonte = Odbc.Query("driver=*DUMMY STRING*", "SELECT #(lf) internalstoreid AS ""id interno estabelecimento""#(lf) ,salesdate AS data#(lf) ,internalproductid AS ""id interno produto""#(lf) ,salesquantity AS venda_quantidade#(lf) ,grosssale AS ""venda bruta""#(lf) ,netsales AS ""venda liquida""#(lf) ,discount AS desconto#(lf) ,tax AS imposto#(lf) ,grosscost AS ""custo bruto""#(lf) #(lf) ,marginvalue AS margem#(lf) #(lf)FROM lopes_prod.fact_sellout #(lf) WHERE salesdate >= convert(datetime2, '"& Text.From(RangeStart) & "') and salesdate <= convert(datetime2, '"& Text.From(RangeEnd) & "')"),
#"Changed Type" = Table.TransformColumnTypes(Fonte,{{"venda_quantidade", Currency.Type}, {"venda bruta", Currency.Type}, {"venda liquida", Currency.Type}, {"desconto", Currency.Type}, {"imposto", Currency.Type}, {"custo bruto", Currency.Type}, {"margem", Currency.Type},{"data", type datetime}})
in
#"Changed Type"

How do I fix this ? I know Nothing about M and a minium about PQ.

Thanks in Advance

[Anonymous]

Progress, Kinda of... My DB is POSTGREE so "convert" is not a valid function.

So I tweaked the code to 

= Odbc.Query("driver={PostgreSQL Unicode(x64)}*ADDADRESSHERE*", "SELECT #(lf) internalstoreid AS ""id interno estabelecimento""#(lf) ,salesdate AS data#(lf) ,internalproductid AS ""id interno produto""#(lf) ,salesquantity AS venda_quantidade#(lf) ,grosssale AS ""venda bruta""#(lf) ,netsales AS ""venda liquida""#(lf) ,discount AS desconto#(lf) ,tax AS imposto#(lf) ,grosscost AS ""custo bruto""#(lf) #(lf) ,marginvalue AS margem#(lf) #(lf)FROM lopes_prod.fact_sellout #(lf) WHERE salesdate >= CAST('"& Text.From(RangeStart) & "' AS TIMESTAMP) AND salesdate <= CAST ('"& Text.From(RangeEnd) & "' AS TIMESTAMP)")

But now I am getting a different error message

Atleast I know that it is correctly retrieving my parameters and turning them into a text inside the query since "13/09/2021 00:00:00" is my RangeEnd Parameter.

The issue now seems to be that the DB uses yyyy-mm-dd 00:00:00 datetime format. So the query needs to transform my parameter written as dd/mm/yyy 00:00:00(and i cant change this in PBI it has to be written this way) into the aforementioned datetime format.

TL:DR: How to I turn my '"& Text.From(RangeEnd) & "' (that returns dd/mm/yyy 00:00:00 as text) i.e: "13/09/2021 00:00:00" into "2021-09-13 00:00:00" By using M functions.

[Anonymous · Accepted solution]

Managed to get it to work!!

Since I do not know how to format a string to the desired way in M as stated bedore from "DD/MM/YYY "to "YYYY-MM-DD"(duo to baiscally not knowing much M) Wh not do it on the DB side ?
Since i am using postgre I am using one of its functions (might not work or need adapting to other SQL servers) I used TO_TIMESTAMP function. it accepts a string as argument (id this context provided dinamically by the RangeStart and RangeEnd Parameters )and converts it to a timestamp that can then be read nativelly by the server so the WHERE clause works as expected.

In the end the final code turned to be 

let

Fonte = Odbc.Query("driver={PostgreSQL Unicode(x64)};server=*DUMMY ADRESS HERE*", "SELECT #(lf) internalstoreid AS ""id interno estabelecimento""#(lf) ,salesdate AS data#(lf) ,internalproductid AS ""id interno produto""#(lf) ,salesquantity AS venda_quantidade#(lf) ,grosssale AS ""venda bruta""#(lf) ,netsales AS ""venda liquida""#(lf) ,discount AS desconto#(lf) ,tax AS imposto#(lf) ,grosscost AS ""custo bruto""#(lf) #(lf) ,marginvalue AS margem#(lf) #(lf)FROM lopes_prod.fact_sellout #(lf) WHERE salesdate >= TO_TIMESTAMP('"& Text.From(RangeStart) & "', 'DD-MM-YYYY' ) AND salesdate < TO_TIMESTAMP ('"& Text.From(RangeEnd) & "' , 'DD-MM-YYYY' )"),

Made some tests, and It works fine Powerbi has no issue with it ! its reduced my refresh time by 1/4 and properly refreshes only the set ammount defined in the incremental refresh (in my tests it is 4 Months) as can be seen in SSMS

So success !

TL:DR. it is possible to bypass the powerquery behavior of not query folding when using a pre defined SQL statement , by adding dynamic parameters via M code integrated with the query supplied to the source independent of the source (ODBC in this case)