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.

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.

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!

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"