Remove duplicates based on specific value

See the attached PBIX file. This is what I returned using Power Query.

EDIT: I see for this image I inadvertently formated TransactionEnd as Date. Should have been DateTime. I've fixed in the PBIX link, but not redoing the image. You may only want dates in your model, not times as well, so format that however you like. But Times are necessary for this to work since some of your records are on the same day and it needs a time to find the true latest value.

 

Here is what I did:

1. Made sure the source table is not set to load.
2. Created a reference to that table and called it "Successful Only" and kept only the Successful records and the ItemReference field.
3. Created another reference to the source and called it "Final Table." Here is where the work starts.
4. Starting with the Final Table, I merged the Successful Only table using the Item Reference field then expanded just the TransactionStatus field, which is now TransactionStatus.1. Now I either have null or Successful.
5. Added a new "TransactionStatus Match" field that returns true or false if the status from the original table matches the new field. = [TransactionStatus] = [TransactionStatus.1]
6. Filtered based on the TransactionStatus.1 field and the TransactionStatus Match field: each ([TransactionStatus.1] = null or [TransactionStatus Match] = true) 
7. I then grouped rows by the ItemReference and used the AllRows aggregation. You can see each embedded table has only those records related to the itemreference for that row.
   1. 
8. Then I filtered out to only return the record with the maximum date. This is a new column with the below formula:
   1. = Table.Max([All Rows],"transactionEnd")
9. This returns a single record. I expanded that record and all fields except the TransactionStatus. We already have that.
10. Removed all unnecessary columns, then changed the data types again. Using nested tables destroys the data types and you want them properly typed before you load.
11. Load the Final Table into DAX for use.

 

Hi Anonymous  

you can group on "itemReference", select "All" in the Operations and name the group-column "ItemData".

Then add this column: 

 

if List.Contains([ItemData][TransactionStatus], "Successful") 
    then [ItemData]{[TransactionStatus = "Successful"]} 
    else Table.SelectRows([ItemData], (x) => x[transactionEnd] = List.Max([ItemData][transactionEnd])){0}

 Also see attached file