Issue with Query Folding for Incremental refresh

[rasika_pawar]

In our fact table, there are some transformations applied in power query editor. And I now want to filter the table by using RangeStart and RaneEnd Parameters. This table is having 2 Date columns which are Transaction Date and DateKey. Transaction Date is of Date datatype and DateKey is of Text type. I tried by using Transaction Date column to filter the data but "view native query" option is disabled. I created column from selection by using Transaction Date and converted it's type to date/time which will be similar to those parameters but still "view native query" option is disabled.  

Then I used a M-Code to filter out the data like Text.from() which enabled "view native query" option but then all the column's type got converted to text.

I tried by performing all the transformations which are applied in power query editor using SQL statement option while loading the data from SQL server in power query editor. But it is not filtering out properly.

Please suggest an option which will filter out our data using those parameters without any errors.

[v-kathullac]

Hi @rasika_pawar,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @rasika_pawar,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-kathullac · Accepted solution]

Hi @rasika_pawar,

To resolve the issue you encountered in Power BI, the best approach is:

1) Keep all foldable transformations (like parameter filters, column removal) in a separate base query.
2) Rename this base query (e.g., SalesData_Staging).
3) Right-click on the base query → choose "Reference" to create a new query (e.g., SalesData_Final).
4) Apply all non-foldable steps (e.g., custom columns, merges, groupings) only in the referenced query.
5) This prevents re-filtering and keeps parameter-based filters intact.
6) Disable loading of the base query (right-click → uncheck "Enable Load") to reduce memory usage.
7) Load only the final query (SalesData_Final) into the data model  In the base query, use "View Native Query" after each step to verify that query folding is still active

Regards,

Chaithanya.

[rajendraongole1]

Hi @rasika_pawar  - You're on the right track by trying to implement incremental refresh using RangeStart and RangeEnd, and enabling "View Native Query" is critical for it to push filters down to the source (folding). However, mixing types and complex transformations in Power Query is likely breaking query folding.

Suggest few points:

Do not cast columns to text or do calculations before filtering.
Check "View Native Query" immediately after the filter step.
If needed, construct a dynamic SQL query using Value.NativeQuery.

Hope this helps.

[rasika_pawar]

Thank you for your suggestion.

But I already tried the ways you suggested. I duplicated the table and delete all the transformations till Navigation and then filtered the data using parameters due to which query folding was enabled. But when I applied the other transformations steps then 2 things happened one is the data which is filtered by using parameters, got filtered back by the transformations applied after filter. And view native query was disabled for those transformations.