A case insensitive approach to sort a table with Power Query (M language)

Have you already tried to sort a table based on a text field? The result is usually a surprise for most people. M language has a specific implementation of the sort engine for text where upper case letters are always ordered before lower case letters. It means that Z is always before a. In the example (here under), Fishing Rod is sorted before Fishing net.

sort-001

The classical trick to escape from this weird behavior is to create a new column containing the upper case version of the text that will be used to sort your table, then configure the sort operation on this newly created column. This is a two steps approach (Three steps, if you take into account the need to remove the new column). Nothing bad with this except that it obfuscates the code and I hate that.

sort-002

The documentation of Table.Sort is really sparse, especially the last parameter comparisonCriteria. I never found any documentation on this parameter and most of the available examples just use the name of one of the fields of the table. Hereby, I’ll give you a trick to apply a case insensitive sort for a table in one step.

The most important thing to understand is that comparisonCriteria doesn’t need to be a column, it can be any function. Based on this, it’s an safe move to change the code {"columnSort"} into {each Text.Upper(_[columnSort])} … and that’s it!

sort-003
let
  Source = Table.FromRecords(  
    {  
      [OrderID=1, CustomerID=1, Item = "Fishing rod", Price = 100.0],  
      [OrderID=2, CustomerID=1, Item = "1 lb. worms", Price = 5.0],  
      [OrderID=3, CustomerID=2, Item = "Fishing net", Price = 25.0],  
      [OrderID=4, CustomerID=3, Item = "Fish tazer", Price = 200.0],  
      [OrderID=5, CustomerID=3, Item = "Bandaids", Price = 2.0],  
      [OrderID=6, CustomerID=1, Item = "Tackle box", Price = 20.0],  
      [OrderID=7, CustomerID=5, Item = "Bait", Price = 3.25],  
      [OrderID=8, CustomerID=5, Item = "Fishing Rod", Price = 100.0],  
      [OrderID=9, CustomerID=6, Item = "Bait", Price = 3.25]  
    }  
  ),
  #"Changed Type" = 
    Table.TransformColumnTypes(Source,{{"Item", type text}}),
  #"Sorted Rows" = 
    Table.Sort(#"Changed Type",{each Text.Upper([Item])})
in
  #"Sorted Rows"
Advertisements

5 comments

  1. You don’t need to wrap the each statement in {} brackets, that’s only needed if there’s a list of sort items.
    You’d think you’d be able to use the “Comparer.OrdinalIgnoreCase” option (which can be used with other commands e.g.Text.Contains and List.Distinct) but no such luck.
    As for trying to find help in the official documentation, good luck. Go to Table.Sort, it makes a brief mention of the Comparison Criteria but no actual explanation, go back to the Table functions page and it mentions the Comparison Criteria and then refers you to the Table.Sort page for examples! So frustrating.

    1. You’re right about wrapping te each statement between curly brackets, it’s not needed. I also share your point of view that the documentation is really sparse about Comparison Criteria.

  2. Further to my earlier comment, it looks like you can’t use this for multiple fields in a Table.Sort, trying it results in “Expression.Error: In fact, removing the Text.Upper and just putting the [fields] in a list gives the same error. You can not compare a list of values. So, when you only have a single item it must automatically strip out the {} brackets so that it’s not a list. So, in conclusion, the {} shouldn’t really be there! Rather annoying as I wanted to use this method to sort on two fields, I guess I’ll have to go back to converting them to upper-case first, sorting on the upper-case fields and then removing them again!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s