Auto generation of incremental filter step for dataflow results in inefficient folding

[fafhrd]

The way incremental refresh is set up in Power BI (PBI) is not the same as for Dataflow. In PBI desktop you manually create the reserved word variables RangeStart and RangeEnd and manually create the filter step using those variables. This means you can place the filter step wherever you like in the Power Query (PQ).

With Dataflow, you have to switch on incremental refresh and the system generates the variables automatically. Then the system also inserts a filter step automatically at the end of the PQ. The problem is the end of the PQ is not usually the best place for an incremental partition step to occur. Furthermore, it is impossible to manually override order of this automatic step.

The problem is that code generated by the folding engine is more efficient if the filtering occurs as close to the start of the PQ as possible. I was hoping that the behaviour of setting up incremental refresh in Dataflow could be made more similar to how it is in PBI Desktop.

I have seen exactly one post about this issue before in 2019. So I hope there is a workaround that you could share with me for this issue.

[YB1]

Has anyone found a workaround for this? 

[Greg_Deckler]

@fafhrd Dumb question because I haven't tried this out, but you can't use Advanced Editor to edit your dataflow and change things around?

[fafhrd]

@Greg_Deckler Yeah you can. Can also click and drag to rearrange in the PQ editor. But if you save and go back into the PQ, you will see the system puts the filter step back at the end automatically.

Your edit isn't respected if you hit save and don't touch it again as well. Can confirm this by rearranging the order, then saving, then exporting the .JSON of the Dataflow. You'll see the filter step is still at the end.