incremental refresh rangestart and query folding

[Abv46]

Hi there!!!

i’m trying to set UP incremental refresh, i’ve followed all the instructions but have two important questions:

-If my data star on 01/01/2008 and end on 01/01/2021 Wich date may use in rangestart and rangeend??

-When set the policies of incremental refresh power bi show the alert o cannot verify m code for query folding, my data is from a dataflow which source is a sql database, but for import table I ve used sql sentences por more accurate import of the table. I don’t know if this affects to query folding since I cannot view “native query” option in the steps of query editor..

thanks in adanvance!

[d_gosbell · Accepted solution]

---

-If my data star on 01/01/2008 and end on 01/01/2021 Wich date may use in rangestart and rangeend??

---

RangeStart and RangeEnd are the names of parameters which you setup in a custom filter over your date column. You don't enter values for them, the incremental processing engine injects values into these parameters.

---

-When set the policies of incremental refresh power bi show the alert o cannot verify m code for query folding, my data is from a dataflow which source is a sql database, but for import table I ve used sql sentences por more accurate import of the table. I don’t know if this affects to query folding since I cannot view “native query” option in the steps of query editor..

---

If you've typed in your own native queries this will block query folding. Folding is the process where M can take multiple steps and "fold" them down to a single SQL query, if you hand type your own SQL then M cannot reliably inject the date filters into this so it throws this warning. This means it would have to grab the entire data set and then filter it on the Power BI side rather than doing the more efficient operation of pushing the date filters into the WHERE clause of a SQL query.

[vissvess]

Is it possible when I use value.native query and try to insert the RangeStart & RangeEnd parameters in the my own query's where clause. Does this allow incremental refresh?
@d_gosbell 

[JWedge]

I have done this successfully but started having problems just this week and the issue looks like it could be the combination of incremental refresh and value.nativequery.  Although my problem is not consistent enough for me to be sure.

[d_gosbell]

This might work, but the only way to be sure would be to run some tests.

I think the incremental refresh configuration window will probably show some warnings as Value.NativeQuery will break query folding, but you can probably ignore these. The whole point of checking if query folding is working is because query folding allows Power Query to generate the WHERE clause for the relevant SQL query (if you are using an SQL source). So if you are using the RangeStart/RangeEnd parameters and manually injecting them in to the native query I think this might still work.