Dataflow Incremental Refresh adding filter as last step

[jonimatix_pp]

Hi,

I am implementing incremental refresh on a dataflow with ODBC data source. I am using RangeStart / RangeEnd inside M code to change the source query, in order to behave similar to query folding. 

However, Power BI is adding autogenerated_for_incremental_refresh filter at the very end. Can this be avoided, or are there ways to solve it?

Thanks.

[Poojara_D12]

Hi @jonimatix_pp 

When implementing incremental refresh in a Power BI dataflow using an ODBC data source, Power BI automatically adds the autogenerated_for_incremental_refresh filter at the end of the query. This happens because ODBC sources generally do not support native query folding, so Power BI tries to apply the incremental filtering separately rather than pushing it down to the source. Since you're already using RangeStart and RangeEnd inside your M code to manually modify the source query, Power BI still appends its own filter, which can lead to inefficiencies or even prevent proper partitioning. Unfortunately, this autogenerated filter cannot be removed entirely, but there are a few workarounds to optimize the process. One approach is to ensure that your M query is structured in a way that Power BI does not need to apply its filter afterward, such as explicitly limiting the dataset before any transformations. Another option is to use a custom SQL query (if your ODBC source supports it) within the Value.NativeQuery function to enforce filtering at the source level. If neither of these works, you may need to consider using a different connector (e.g., a native database connector that supports query folding) or implement incremental logic outside Power BI, such as staging the filtered data in a data warehouse before importing it into Power BI.

[v-pagayam-msft]

Hi @jonimatix_pp ,
We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.If my answer resolved your query, please mark it as "Accept it as solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[jonimatix_pp]

Hi @v-pagayam-msft 

Can you explain how to apply rangestart/end filtering directly in the native query (odbc)?

As per video above, I tried to change the last step from: 

#"Activity-646174655F66696C746572-autogenerated_for_incremental_refresh" = Table.SelectRows(#"Renamed columns", each DateTime.From([date_filter]) >= RangeStart and DateTime.From([date_filter]) < RangeEnd)

to

#"Activity-646174655F66696C746572-autogenerated_for_incremental_refresh" = #"Renamed columns"

but after saving the dataflow, the changesare reverted back.

[v-pagayam-msft]

Hi @jonimatix_pp ,
Thanks for following up! Upon my understanding,To apply RangeStart and RangeEnd filtering in a native query for an ODBC source, use the Odbc.Query() function. Below example might help you:
let
Source = Odbc.Query("your_connection",
"SELECT * FROM your_table
WHERE date_filter >= '" & DateTime.ToText(RangeStart, "yyyy-MM-dd HH:mm:ss") & "'
AND date_filter < '" & DateTime.ToText(RangeEnd, "yyyy-MM-dd HH:mm:ss") & "'")
in
Source

Please refer the document for more information
https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview 

If this resolved your query, please consider accepting it as solution.

Thank you.

[freginier · Accepted solution]

Hey there!

Power BI automatically adds the autogenerated_for_incremental_refresh filter as a part of its internal process to manage incremental refresh. This filter ensures that only data within the defined range (usually based on the RangeStart and RangeEnd) is considered during the refresh process. It ensures that only the new or changed data gets loaded into your dataset, improving refresh times and performance.

Unfortunately, you cannot directly remove or bypass this automatically generated filter when using incremental refresh. The autogenerated_for_incremental_refresh filter is part of Power BI's internal mechanism for tracking and managing incremental data loads.

To work around it you could try: 

Adjust the Position of Your Custom Filter: You can position your custom filters (like those based on RangeStart and RangeEnd) earlier in the query steps. This can help in some cases, ensuring that the data is filtered appropriately before the autogenerated_for_incremental_refresh filter is applied. However, it won't remove the autogenerated filter—it just ensures your filtering logic is applied before it.

Use Query Folding: Ensure that your query folding is working properly with the ODBC source. Query folding will ensure that filtering happens at the source level, which can significantly reduce the amount of data transferred during the refresh. This will reduce the impact of having an additional filter in the query.

Hoep this helps!

😁😁

[jonimatix_pp]

Thanks for your reply.

So anything changed since this video was published https://www.youtube.com/watch?v=x7q1DHf8wE4&t=257s ?

[v-pagayam-msft]

Hi @jonimatix_pp ,
Thank you for the reaching out to us!
We would like to help you in clarifying the things.

No, the core behavior of Incremental Refresh in Power BI remains the same. Power BI still adds the autogenerated_for_incremental_refresh filter. However, there are ways to control how and where filtering happens to improve performance.

For ODBC sources, applying filtering directly in the native query (Odbc.Query()) or using a database view can help optimize performance.

For further details, you may check Microsoft’s official documentation on Incremental Refresh:
https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview 

Hope this helps.If so,consider accepting it as solution.

Regards,
Pallavi G.