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.

Ascending order (from smallest to largest):

• type null
• type time (early to late)
• type date (past to future)
• type datetime (past to future)
• type datetimezone (past to future)
• type duration (short to long)
• type number (negative to positive)
• type logical (false to true)
• type text (Unicode sequence, so 1,2,3 … A,B,C … a,b,c … 漢 .. etc)
• type binary (small to big)

So Value.Compare(null,7) returns -1 while Value.Compare(Duration.From(0.2),Time.From(0.5)) returns 1.

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.

List.Sort

• Order as number (Order can either be Order.Ascending ( = 0) or Order.Descending ( = 1))
• TransformItem as function (this function takes a list item for its argument which it later transforms)
• {TransformItem as function} as list
• {TransformItem as function, Order as number} as list
• {TransformItem as function, Order as number, Comparer as function} as list
• Comparer as function (The function takes two list items for its two arguments which it later compares) 

Table.Sort

The first, most known style has the forms:

• ColumnName as text (Order.Ascending is assumed)
• {ColumnName as text} as list
• {ColumnName as text, Order as number} as list

The second style has the forms:

• TransformRecord(x as record) as function
• {TransformRecord(x as record) as function} as list
• {TransformRecord(x as record) as function, Order as number} as list

A good example is presented by Cédric Charlier here.

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.

equationCriteria and the other comparers

Although equationCriteria is not used in the sort functions, equationCriteria can be or contain a comparer function. Chris Webb writes about equationCriteria and the other comparers (Comparer.FromCulture, Comparer.Ordinal and custom comparers) here.