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 Anonymous
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.
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.