Filter a column with duplicate values based on the highest Index value

Question

Hello.&nbsp;I am struggling to find a solution for something that I believe should be quite simple. I have a column of values (some of which are duplicates) that I want to filter to show only those with the highest Index number (example below).&nbsp;&nbsp;Current Data::&nbsp;Case Ref:&nbsp; &nbsp; &nbsp; &nbsp; Index No.&nbsp;545059&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0545059&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp;545060&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2545060&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3545060&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4545061&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5545061&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6545061&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7545062&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8545062&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9545063&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp;&nbsp;Result i'm trying to achieve:&nbsp;Case Ref:&nbsp; &nbsp; &nbsp; &nbsp; Index No.545059&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1545060&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4545061&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7545062&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 9545063&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp;&nbsp;&nbsp;Thanks in advance.

slorin · Accepted Answer

Hi
Group by Ref and choose Max Index
&nbsp;
= Table.Group(YourSource, {"Case Ref:"}, {{"Index No.", each List.Max([#"Index No."]), type number}})
or
= Table.Sort(   Table.Distinct(     Table.Buffer(       Table.Sort(         YourSource,         {{"Index No.", Order.Descending}})),     {"Case Ref:"}),   {{"Index No.", Order.Ascending}})
&nbsp;
Stéphane

Forum Discussion

Filter a column with duplicate values based on the highest Index value

2 Replies

Related content

Duplicate ranking

Highest Status

Highest value of a duplicate

2nd Highest Value

remove duplicate value within a row