Detect Changes Incremental Refresh

[Santidlpena94]

Hello Everybody

Im using incremental refresh in a dataflow in a big fact table and im not sure if im using detect changes correctly. I have the following setup;

It is my understanding that the "detect changes" function should be activated if there are dates from the last 7 days in the "fechaact" column, but I see that it only works when the "fechacreacion" column AND "fechaact" are in the last 7 days . Am I doing something wrong? Or is that how it works?

[rajendraongole1 · Accepted solution]

Hi @Santidlpena94 - 

Your understanding of how the "Detect Changes" functionality works in incremental refresh in Power BI Dataflows is slightly off. Let me clarify:

How "Detect Changes" Works
When you enable Detect Changes in Power BI Dataflows:

You specify a column (or columns) that Power BI uses to identify rows that have changed.
Power BI will only refresh partitions that include rows where the values in the specified column(s) have changed within the defined window (in your case, the last 7 days).
When multiple columns are selected for Detect Changes (like fechacreacion and fechaact), all selected columns must meet the condition for a row to qualify for refresh. This means:

A row is considered "changed" only if both fechacreacion and fechaact have values within the last 7 days.
This behavior explains why it seems like changes are only detected when both columns meet the criteria.

What You Might Be Doing Wrong
Selecting Multiple Columns: By selecting both fechacreacion and fechaact, you are making the detection logic stricter. This might not align with your requirement.

If your intent is to detect changes based on either column, this won't work as expected.
Column Purpose: fechacreacion likely reflects the creation date of the row, which doesn't change. Incremental refresh typically tracks updates based on a column like fechaact (last modified date).

Recommended Fix
To ensure the setup works as intended:

Use Only fechaact for Detect Changes:

If you want to refresh rows based on changes or updates, only select fechaact in the Detect Changes configuration.
This way, any row with a fechaact value in the last 7 days will trigger a refresh.
Test Incremental Refresh:

After making the change, test your incremental refresh setup by adding/updating rows and verifying that only the intended partitions are refreshed.