Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
adeeln
Post Patron
Post Patron

Incremental refresh is not working with soft deleted records


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

8 REPLIES 8
SqlBobScot
Helper II
Helper II

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)

@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

 

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
Super User
Super User

Hi @adeeln 


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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

GilbertQ
Super User
Super User

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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

adeeln_0-1621836482783.png

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
Super User
Super User

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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

adeeln_0-1621831082173.png
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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors