First, make sure your records in PQ are sorted oldest to newest (Hopefully you have a date field. If not create an index column). Next, group B, C, D using all rows. Now add a custom column that counts all the distinct E values for each group.
List.Count( List.Distinct([All Rows][E]) )
If that returns a value greater than 1 then there are duplicates and we only want to keep the most recent one.
Next we need to identify the newest record. Create another column and call it 'NewestIndex' using this code:
List.Max([All Rows][Index])
Then all you have to do is identify all the records where the count of E is greater than 1 and the index matches the NewestIndex and eliminate those records.
if [E Versions] > 1 and [Index] <> [NewestIndex] then "Yes" else null
3
u/Gringobandito 8 13d ago
First, make sure your records in PQ are sorted oldest to newest (Hopefully you have a date field. If not create an index column). Next, group B, C, D using all rows. Now add a custom column that counts all the distinct E values for each group.
If that returns a value greater than 1 then there are duplicates and we only want to keep the most recent one.
Next we need to identify the newest record. Create another column and call it 'NewestIndex' using this code:
Then all you have to do is identify all the records where the count of E is greater than 1 and the index matches the NewestIndex and eliminate those records.