Extra documentation for Sorting Functions and Comparing Functions in Power Query

This post not only serves as extra documentation for the native sort functions and comparers but also to give some background information for the next post on this blog.

Comparers

There are several comparers (also known as comparison functions) native to Power Query. That is Comparer.Equals, Comparer.FromCulture, Comparer.Ordinal, Comparer.OrdinalIgnoreCase (which can be found here) and Value.Compare (which can be found here). The comparers take two arguments (x, y) and (apart from Comparer.Equals) return either -1 (if x < y), 0 (if x = y) and 1 (if x > y). These comparers can compare values of the same type e.g. text with text, number with number, duration with duration but only Value.Compare and Comparer.FromCulture can compare values of different types e.g. number and text. As Value.Compare has this flexibility and thus robustness but is also non-specific (unlike Comparer.FromCulture), it is used as the default comparer in the sorting functions (List.Sort and Table.Sort). Sorting causes values to separate into their different types in a particular order.

Values of other types such as type list, type type, type function, type list, type record and type table cannot be compared using a native comparer (an error will occur). You will need to use a custom comparer. The sort functions can actually utilise a custom comparer provided you use the right comparisonCriteria.

For information about Comparer.FromCulture, Comparer.Ordinal and custom comparers see bottom of this page.

comparisonCriteria combinations for the sort functions

There are two native sort functions, List.Sort and Table.Sort. Their last argument is comparisonCriteria which can be in different styles of multiple forms.

Forms of both the first and second style can be put together in a list for multi level sorting

E.g. {“Col1”, {“Col2”, Order.Ascending},{“Col3″}} could be a valid comparisonCriteria.
and in the case of example presented by Cédric Charlier:
{{each Text.Upper([Item]),Order.Descending},”CustomerID”} would also be a valid comparisonCriteria.

The last style has one form:

Comparer(x as record, y as record) as function

The last one, I think is the most interesting. It is the most powerful option but as there are no native comparers that can directly work with records in Power Query you will need to use a custom comparer. Marcel Beugelsdijk does it quite nicely here.