Remove duplicates in Power query editor

Question

I have below data:

Con                             Payment Status  Count
HUMANABRATTEN,MICOL9/20/2021    Resubmitted      15
HUMANABRATTEN,MICOL9/20/2021    In-Process      1

they have exact same length but when I try to remove duplicate it always removes the "Resubmitted" whereas I want the high count Payment status

Normally in Excel, when we remove duplicate from any Data it always return the first value and remove 2nd value. IDK why its not working in Power Query

Answer 1

Power Query does not necessarily return results in the order you might expect. Even the sorts are unstable, if I recall correctly.

For your problem, one solution would be to use Table.GroupBy and then extract the desired results. In your case it seems to be the Max Count, and the Payment Status that is in the same row as Max Count.

eg:

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Con", type text}, {"Payment Status", type text}, {"Count", Int64.Type}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Con"}, {

//Return the Payment Status cell that is in the same row as Max Count
        {"Payment Status", each _[Payment Status]{List.PositionOf(_[Count],List.Max(_[Count]))}},

//determine the Max Count
        {"Count", each List.Max([Count]), type nullable number}})
in
    #"Grouped Rows"