Forum Discussion

RossS's avatar
RossS
Icon for Helper II rankHelper II
7 months ago
Solved

Remove duplicates based on Vendor ID and Date

I am looking to remove duplicates, but not just by date, but by vendor ID. This will mean that each most recent occurance of a barcode, from each vendor, will load. Looking at the below exmaple, I sh...
Need Help
  • RossS's avatar
    RossS
    7 months ago

    Thanks for the responce.

     

    I struggled to replicate this as the steps take so long in Power Query to load. I manage to solve my issue as there was a column in my table for the latest price, true or false, for each product.

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 months ago

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.