Question

Removing Duplicate Transactions While Keeping the Latest Entry
Your sales dataset contains duplicate transaction IDs due to incorrect data entry. You need to remove duplicates while keeping only the latest transaction per ID. How can you achieve this in Power Query or DAX while ensuring accuracy?

Answer 1

An approach to eliminate duplicate transactions while retaining only the last one is either through Power Query or DAX.

Solution 1: Power Query (Preferred Data Cleaning)

Sorting Data by Transaction Date

Open Power Query Editor.

Sort the dataset based on Transaction ID (Ascending) and sort Transaction Date (Descending), which will bring the latest entry first.

Remove Duplicate, Keeping Latest Entry

Select the Transaction ID column,

Click Remove Duplicates from the Home ribbon.

This will result in Power Query keeping the first occurrence, which, in this case, is now the latest because of sorting.

Solution 2: DAX (For Dynamic Calculations in Reports)

If the dataset cannot be preprocessed, use a DAX measure or calculated table.

DAX Table (Filtered Latest Transactions)

Latest Transactions = 
VAR LatestDates = 
    ADDCOLUMNS( 
        SUMMARIZE( Sales, Sales[Transaction ID], "LatestDate", MAX( Sales[Transaction Date] ) ), 
        "LatestEntry", 
        LOOKUPVALUE( Sales[Transaction Date], Sales[Transaction ID], Sales[Transaction ID], Sales[Transaction Date], MAX( Sales[Transaction Date] ) )
    )
RETURN 
FILTER( Sales, Sales[Transaction Date] = [LatestEntry] )

• This table keeps only the latest transaction per Transaction ID dynamically.

DAX Measure (For Reporting)

Latest Sales Amount = 
VAR LatestDate = MAX( Sales[Transaction Date] )
RETURN 
CALCULATE( SUM( Sales[Amount] ), Sales[Transaction Date] = LatestDate )

Ensures only the latest sales amounts are considered in reports.