Forum Discussion
Resolver IQuery Folding Stops for Incremental Refreshes
Dear community,
when setting up incremental refresh, my query stops being folded back to the source.
Any idea why this is so?
Any idea, how I can get Power BI to keep folding the query?
Details:
I have a report connected to an OData source with a filter on two date fields and a status field.
When I refresh the dataset, both in Desktop and Premium Capacity Service, Query Folding seems to be taking place.
I checked both, in the Query Editor's Step Diagnostics
and in the log file of the data source's OData service.
The filters are folded back to the source OK, query folding appears to be working.
Then I want to set up incremental refresh of the table.
I introduce the two reserved parameters RangeStart and RangeEnd and apply them to yet an other date fields (CREATE_DATE).
As "current values" I set 04.03.2021 00:00:00 and 06.03.2021 00:00:00. The field CREATE_DATE is also of Type DateTime.
Checking now in Step Diagnostics, no filters are passed to the source anymore.
Query folding is gone.
Same in the service log on the source's server:
This also does not change when I set up the incremental refresh policy for the table and publish it to the Premium Capacity service.
The full table is read and filtering is only applied then later on by the engine.
(I can tell this as I now the data transfered in the response is > 15MB large, but only 8 records end up in the table.)
Any idea why this is so?
Any idea, how I can get Power BI to keep folding the query?
Many thanks for your help!
"SOLVED"!
I found an interesting half sentence in edhans solution/reply to the following post:
Query Folding & Incremental Refresh"It reads the entire query, then folds everything it can [...]."
Turns out Power BI in my case does not fold the RangeStart and RangeEnd parameter into the query. Why not is an other riddle yet to solve.
But since in my above example, the filtering with RangeStart and RangeEnd was done in the same step as the other filtering, it abandoned the entire filter step.
Now it placed the filtering in two steps.
First my "normal" filtering including STATUS etc.
THEN the filtering on the Range parameters.
And, voila, the "normal" filters are folded back into the query.Hi edhans
thanks for your input.
I am not entirely sure though how your date filtering with the date coming from an additional blank query is different from doing the AddDays function as a previous step in the same query like I do for the "normal" date filters shown above (purple frame in screenshot)?
I managed to find the true root cause of the range parameters not folding into the query though.
Now it works!
Check out my spereate thread on this topic.
( Incremental Refresh Should RANGESTART also fold )
3 Replies
- JSiebrecht
"SOLVED"!
I found an interesting half sentence in edhans solution/reply to the following post:
Query Folding & Incremental Refresh"It reads the entire query, then folds everything it can [...]."
Turns out Power BI in my case does not fold the RangeStart and RangeEnd parameter into the query. Why not is an other riddle yet to solve.
But since in my above example, the filtering with RangeStart and RangeEnd was done in the same step as the other filtering, it abandoned the entire filter step.
Now it placed the filtering in two steps.
First my "normal" filtering including STATUS etc.
THEN the filtering on the Range parameters.
And, voila, the "normal" filters are folded back into the query.- edhans
Community Champion
Something else you can do JSiebrecht is have Power Query calculated those variables for you. So a blank query that is:
let Source = Date.AddDays(DateTime.Date(DateTime.LocalNow()), -3) in Sourceif you call that varStartDate, then when you have a filter that does this:
Table.SelectRows(TableName, each [Date] < varStartDate)It will actually do a SQL WHERE clause that feeds the date calculated in varStartDate as a text amount the query folding supports.
- JSiebrecht
Hi edhans
thanks for your input.
I am not entirely sure though how your date filtering with the date coming from an additional blank query is different from doing the AddDays function as a previous step in the same query like I do for the "normal" date filters shown above (purple frame in screenshot)?
I managed to find the true root cause of the range parameters not folding into the query though.
Now it works!
Check out my spereate thread on this topic.
( Incremental Refresh Should RANGESTART also fold )
