Forum Discussion

GFire's avatar
GFire
Helper I
1 year ago
Solved

Eliminate duplicate records while keeping the most recent record.

                  Hi,   By default, Power Query keeps the oldest record and deletes the newest record when removing duplicates. I need the opposite: delete the oldest record and keep...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    1 year ago

    Assuming the latest is the last entry for any set of duplicates, you can

    • Group by Invoice Number
    • Return only the last row each subtable
    let

//Read in your data
    Source = Csv.Document(File.Contents("C:\Users\ron\Desktop\Dataset.csv"),[Delimiter=";", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"N_Invoice", type text}, {"Index", Int64.Type}}),

//Group by N_invoice and retain the last row
    #"Grouped Rows" = Table.Group(#"Changed Type", {"N_Invoice"}, {
            {"Latest", each Table.Last(_), type [N_Invoice=nullable text, Index=nullable number]}}),

//remove column and re-expand
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"N_Invoice"}),
    #"Expanded Latest" = Table.ExpandRecordColumn(#"Removed Columns", "Latest", {"N_Invoice", "Index"}, {"N_Invoice", "Index"})
in
    #"Expanded Latest"

     

    This will also retain the original ordering of the entries. You can see in the screenshot below, FV-084482 is in the second row of both your original data, as well as the dedup'd data, even though the "latest" invoice was found near the bottom of the table as indicated by the associated Index number.

     

    Note that Table.Group is not guaranteed to retain your original row order, which would be important in the above algorithm. However, others have reported that the presence of an Index column ameliorates this issue. So ensure that your actual data has an index column (or add one if it does not). 

    If there are issues of that nature, then merely change the custom aggregation in the Table.Group function to: 

     {"Latest", (t)=> Table.SelectRows(t, each [Index]=List.Max(t[Index])), type table [N_Invoice=nullable text, Index=nullable number]}})

     

     

    If you want a different order, please specify.

     

     

     

GFire's avatar
GFire
Helper I
1 year ago

Hi,

 

It doesn't work. For example: N_Invoice number FV-084497 is in the dataset with index numbers 5 and 59. Once the code was applied, the resulting table removed index:59 and retained index:5.

Nasif_Azam's avatar
Nasif_Azam
Super User
1 year ago

Hi GFire ,

Thanks for your feedback. Let's try this M code with confident:

let
    Source = ... ,  // Replace with your actual source
    Grouped = Table.Group(Source, {"N_Invoice"}, {
        {"TopRow", each Table.Sort(_, {{"Index", Order.Descending}}){0}, type record}
    }),
    Result = Table.FromRecords(Grouped[TopRow])
in
    Result

What That M Code Do:

  • Groups the table by N_Invoice

  • Sorts each group in descending order by Index

  • Picks the first row from each group (i.e., the most recent one)

  • Flattens it back into a normal table

 

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.


Best Regards,
Nasif Azam