Power Query – how to find a nth smallest or largest value in table or group

This article describes how to find the second lowest value, the third latest date… in Power Query.

Let´s see how to find out this from the whole table and from subgroups.

Let´s use this as a source:

Finding the second highest value (second latest date)

Let´s write this formula into a Custom column:

• List.Sort(
     MyTable[Meeting],
     Order.Ascending)
  {1}

Because:

• MyTable [Meeting] – takes a “Meeting” column and turns it into a list / list.
• List.Sort – sorts list. The second optional argument, which defines the sorting direction. It´s optional – if omitted, it is ascending by default.
  {1} – returns the second item from list (in Power Query it is numbered from zero, therefore “1” represents the second item)

Btw, if you need the lowest or highest value, it’s easier:

• List.Min(MyTable [Meeting])

resp:

• List.Max(MyTable [Meeting])

Finding the second highest value according to individual clients

To get the same calculation for every subgroup (in this case for every single customer), we can use the formula in grouping.

It can be written directly in M (but you can not use the Custom column):

• = Table.Group(
       MyTable,
       {“Customer”},
       {{“Second earliest meeting date by customer”, each List.Sort([Meeting],Order.Ascending){1}, type nullable date}})

If you don’t like direct writing of M, you can start with grouping by any function, and then just overwrite a small piece of formula.