Forum Discussion
Dataflow Incremental Refresh adding filter as last step
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!
😁😁
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.