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.
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 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 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.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.