What is the function of Detect Data Changes in Refresh?

[PowerRon]

Hi 

below is my refresh, RangeStart and RangeEnd based on creation-date, Detect Data Changes based on last-change-date

I don't understand the addition of Detect Data Changes. Did some testing.

TEST 1
Had two rows with creation-date and last-change-date 2022-02-04.
Change row 1: currency from EUR to ABC
Change row 2: currency from EUR to XYZ, last-change-date to 2022-02-06
Both rows were changed after refresh. Both are within the 2 months refresh, but my expectation was only row 2 would change because of changing last-change-date

TEST 2

Had two rows with creation-date and last-change-date 2021-12-22.
Change row 1: currency from EUR to ZZ1 and last-change-date to 2021-12-28
Change row 2: currency from EUR to ZZ2, last-change-date to 2022-02-14
Nothing changed after refresh. Probably because creation-date is not within the 2 months refresh.

So what then really is the advantage of having that Detect Data Changes checked?
My opinion was that it would then only detect those rows in the refresh period of 2 months, for which last-change-date changed.
But in TEST 1, row 1 is also changed without having changed the last-changed-date.

Please help
Ron

[Heniekwpracy]

In your example (case 1) both rows got refreshed because the MAX(last-change-date) changed for the partition 2022-02, and so it happens that both rows belong in this partition. Partitions are defined by the column where you put rangeStart/End parameters (creation-date in your case).

Case 2: exactly, archived periods are not refreshed at all, only the initial load. That is why we only use this when we know that hitorical records cannot change.

[selimovd]

Hey @PowerRon ,

the idea behind this column is that you have an additional column in your SQL Server like a "Change Date" or a "Load Date". This is quite common in a data warehouse to maintain the data load to the data warehouse.

Like this Power BI can only monitor the "Load Date" column and load the rows that are changing in the source. For more information check the description in the documentation:
https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview#optional-setting...

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

[PowerRon]

Hi 
I understand, We have a creation datetime, which I use for the incremental refresh RangeStart RangeEnd, and we have a last change datetime, which I use for Detect Data Change. But If you look at my test's, why doesn't it do what I expected?

@soheil 

@parry2k 

[v-easonf-msft · Accepted solution]

Hi, @PowerRon 

Please refer to Incremental refresh and real-time data for datasets .

The Detect data changes setting enables even more selective refresh. You can select a date/time column used to identify and refresh only those days where the data has changed. This assumes such a column exists in the data source, which is typically for auditing purposes. This should not be the same column used to partition the data with the RangeStart and RangeEnd parameters. The maximum value of this column is evaluated for each of the periods in the incremental range. If it hasn't changed since the last refresh, there's no need to refresh the period. In this example, this could potentially further reduce the days incrementally refreshed from 3 to 1.

---

Related  tutorial:

Power BI Incremental Refresh - Understanding Detect Data Changes 

Best Regards,
Community Support Team _ Eason