Forum Discussion
Handling of Deleted data in incremental refresh
Hi All I wanted to understand how the rows which are deleted from table are handled in incremental refresh dataset. Does it identify the deleted rows and delete from dataset OR Do we need to do ...
- GilbertQ What Arthemis_Runner said, I'm seeing the same issue.
- I have a record with
DateCreated: 2022-01-01
DateUpdated: 2023-01-01
DateDeleted: NULL - My incremental refresh is functioning properly as if I change DateUpdated to 2024-01-01, it will trigger the partition covering DateCreated: 2022 as the MaxDateUpdated has changed since it was last triggered.
- However, if update the DateDeleted to 2024-01-01 andI change the filtering portion of my SourceExpression from
#"Filtered Rows" = Table.SelectRows (Data, each [DateCreated] >= RangeStart and [DateCreated] <= RangeEnd)
to
#"Filtered Rows" = Table.SelectRows (Data, each [DateCreated] >= RangeStart and [DateCreated] <= RangeEnd and [DateDeleted] = null)
then the partition is not triggered as the record with the updated DateUpdated which would trigger the partition has (I'm guessing here) been filtered out prior to the partition being triggered due to the record no longer have DateDeleted of NULL.
In summary I need to have a record be soft deleted (have DateDeleted go from NULL to X, have DateUpdated go from Y to X), and for that change to DateUpdated to trigger the partition, and during the ingestion of that partition filter out the record that caused that partition to trigger as it now has a value for DateDeleted.
Any insights would be greatly appreciated. Seems maybe something has changed as I've watch a couple of Patricks videos and the apparent assumption that you can just filter out the records triggering the paritition to process doesn't seem to hold up anymore.
FYI: I've also tried the variation below to no avail. Results are the same.
#"Filtered Rows" = Table.SelectRows (Data, each [DateCreated] >= RangeStart and [DateCreated] <= RangeEnd),
#"Filtered Rows2" = Table.SelectRows (#"Filtered Rows", each [DateDeleted] = null)
MichaeldiasHelper I
Getting the same issue. Came up with Patricks solution independently but it does not work.
Using RLS seems like a fudge as it will not affect RLS exempt users.
Using intermediate dataflows will work but that is overkill.
I'm thinking of just taking the data in but blanking out attributes in the record.
At a later date a custom full refresh of any partitions with DateDeleted flag records can be done- Michaeldias
Latest answer from Guy in a Cube is to use Custom polling expressions
Handling deletes within Incremental Refresh in Power BI
Advanced incremental refresh and real-time data with the XMLA endpoint in Power BI - Power BI | Microsoft Learn
Testing it now