Forum Discussion

ValeriaBreve's avatar
ValeriaBreve
Post Partisan
3 years ago
Solved

Remove duplicates

Hello, I have a table as per below. I need to remove duplicates so that one number of PO stays with each couple VBELN/POS, it does not really matter in which order. I imagine this need sto be recur...
Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    Hi, ValeriaBreve this works on your test data but I am not sure about real life data...

     

    let
    Source = your_table,
    groups = Table.Group(Source, {"PO"}, {{"VP", each List.Zip({[VBELN], [POS]})}}),
    lst = List.Buffer(groups[VP]),
    vp_txf = 
        List.Accumulate(
            lst,
            {},
            (s, c) => [a = List.Difference(c, s), b = s & {List.First(a)}][b]
        ),
    z = Table.FromColumns({groups[PO], vp_txf}, {"PO", "VP"}),
    extract = Table.TransformColumns(z, {"VP", each Text.Combine(List.Transform(_, Text.From), "@@@"), type text}),
    split = Table.SplitColumn(extract, "VP", Splitter.SplitTextByDelimiter("@@@", QuoteStyle.Csv), {"VBELN", "POS"})
in
    split

     

AlienSx's avatar
AlienSx
Super User
3 years ago

ValeriaBreve List.Accumulate goes over the list of VBELN/POS pairs associated with each PO. One by one. Variable "c" is your current item in line while "s" is what you have accumulated so far.  List.Difference (c, s) seeks all VBELN/POS pairs (from current "row") we have never seen before and adds first pair found to the accumulator vairable "s". Then goes next step with updated "s".