Remove duplicates based on Vendor ID and Date

Hi RossS 

Sort your data by date in descending order. Add a custom applied step with Table.Buffer to store the sorted data into memory. Select Vendor ID column and then remove duplicates.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjExNVPSUTI3t7CwtDSwBDINDPUNjPWNDIzMlGJ1opUMjQyNgKJmZqZmpkAEUmCKogCLCaaETDAipICgG4wIuQFJQSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Vendor ID" = _t, Barcode = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vendor ID", Int64.Type}, {"Barcode", Int64.Type}, {"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Descending}}),
    Table.Buffer = Table.Buffer(#"Sorted Rows"),
    #"Removed Duplicates" = Table.Distinct(Table.Buffer, {"Vendor ID"})
in
    #"Removed Duplicates"

Hi RossS ,

I'm able to mimic your scenario.

If your data volume is low, you can use the Table.Buffer option. However, if your volume is high, using Table.Buffer will increase the query processing overhead. Here are the reasons why:

1. It loads all the data into memory before processing.
2. It breaks query folding.
3. It is ideal for small lookup tables but not for fact tables (which I assume is the case with your transaction fact).

A solution I would prefer is to use the following approach: sort, then group, and finally take the first record.

Query:

let
    Source = Excel.Workbook(File.Contents("C:\Users\natar\Downloads\Community\Dedup_VendorBarcode.xlsx"), null, true),
    RawData_Sheet = Source{[Item="RawData",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(RawData_Sheet, [PromoteAllScalars=true]),

    #"Typed" = Table.TransformColumnTypes(#"Promoted Headers", {
        {"VendorID", Int64.Type}, {"Barcode", Int64.Type},
        {"Date", type date}, {"Price", type number}, {"Description", type text}
    }),

    #"Sorted" = Table.Sort(#"Typed",{{"Date", Order.Descending}}),

    #"Grouped" =
        Table.Group(
            #"Sorted",
            {"VendorID","Barcode"},
            {{"Latest", each Table.First(_), type record}}
        ),

    #"Expanded" =
        Table.ExpandRecordColumn(
            #"Grouped",
            "Latest",
            {"Date","Price","Description"}
        )
in
    #"Expanded"

Raw data 

Transformed data :

PBIX : Latest data.pbix 

Thanks 
If this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way faster

RossS 

In Power Query (one step):

Sort by Date descending (newest first)

Add Index column

Buffer table: = Table.Buffer(#"Added Index")

Select Vendor ID + Barcode + Index → Remove Duplicates

Remove Index, expand other columns from original

Hi RossS ,

If the response shared by danextian , Natarajan_M , Kedar_Pande  aligns with your expectations, please take a moment to review it and let us know if you need any additional details or clarifications.

Thank you all for your valuable support.

Regards,

Yugandhar.

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Vendor ID"}, {{"Count", each Table.Max(_,"Date")}}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Barcode", "Date"}, {"Barcode", "Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Count",{{"Vendor ID", type text}, {"Barcode", type text}, {"Date", type date}})
in
    #"Changed Type"

Hope this helps.