Remove duplicates based on Vendor ID and Date

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