Incremental refresh is not working with soft deleted records

[adeeln]

Incremental refresh is working fine for new inserted and soft deleted records...

For example if I have inserted new entry and set lastupdated time to now...
I can see record added on service with incremental refresh... Perfect

After few time i update isDeleted =1
Then refresh the dataset I see it works. And records are not found on power bi service... Perfect.

Somehow it's not working for old records..
Very strange behaviour, when i set records deleted=1 for old entries with lastupdated=now
i can't see changes reflecting on service.
But The only case is working if i add new record in table... then Power BI reflect the changes for old and new.
Please help.
Thanks

[SqlBobScot]

Bit of a late reply.  There is one missing step from the deletion of a row.

Set IsDelete = True and ingestionDate = CurrentDateTime.  When the incremental refresh is run, Detect Data Change will look at ingestionDate for any records where the value has changed in the last 24 hours.  Also setting a filter in Power Query Editor to only return records where  IsDelete = False is also required. Check out the video by Patrick LeBlanc of Guy In a Cube he explains it much better than me (https://www.youtube.com/watch?v=JsJWBr1_ktQ)

[shaf25]

@SqlBobScot if we filter in the power query editor for deleted records,refresh will occur only for other records.It will not recognize that the data is deleted if only isdeleted flag is updated to 1 in a partition and no other records are changed

[SqlBobScot]

When the record is updated to set IsDelete = True 

Is the field ingestionDate also updated to as well to the current datetime.

Also if you increase the Refresh rows for changes in the last 7 days what difference does that make?

[GilbertQ]

Hi @adeeln 

Could you not build this into your view you are using?

[GilbertQ]

Hi @adeeln 

You would then have to either re-process those partitions. I see you have got the detect data changes, can you confirm that it is configured correctly?

[adeeln]

@GilbertQ yes, I have confirmed my detect data changes working fine. But In the case of the delete row I am getting duplicate rows that's why I am using Is Delete = False filter.

I have created an Is Delete column in a database (as per the video that link I have attached in my previous post). According to this Is Delete column filter (Is Delete = False ) the deleted column (where Is Delete = True ) will never come in Power BI. If I removed this filter (Is Delete = False ), I will get a duplicate error on Power BI Service.

[GilbertQ]

Hi @adeeln 

Could this be, because you are only doing an incremental refresh on the last N (number of days) so only those partitions are being refreshed.

While if you have older data that is outside the incremental refresh policy that data is not being updated?

[adeeln]

@GilbertQ thanks for your response.
I have applied Incremental refresh only for "Last One Day". You can see this in the below image.

The requirement is if we delete any record from our database. Then how we will be deleted this record from the power bi report in which we have implemented an Incremental refresh.
For Example, I have data from "5/24/2015" to "5/24/2021". I implement Incremental refresh in which data rows store for last 5 years (from "5/24/2016" to "5/24/2021" ) and Data refresh only for the Last one Day (from "5/23/2021" to "5/24/2021" ). After implementation of Incremental refresh, I publish the report on power bi service first time this report refresh the whole data (from "5/23/2021" to "5/24/2021" ) after the first-time refresh that data will never be refreshed except for the Last one day data. The problem begins from there if I delete any row that presents in that range (from "5/24/2016" to "5/22/2021" ). How I will delete them I will try this Guy In Cube Soft Delete solution but this solution does not work.
Regards
Adeel