Sorting a table based on your own criteria with Power Query (M language)

Earlier, I published a blog about A case insensitive approach to sort a table with Power Query (M language) and I received a few pertinent comments from “Nick”. I agree with most of his comments and some remarks about the syntax were fully valid but the last part of one of his comment was not true (or at least is not true anymore). To quote him:

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

I’m a bit overloaded and I took a very long time to check. But at the end, it’s really possible to define multiple criteria to sort a table.

To illustrate the how-to, I’m starting back from my previous example. I’ll now sort the dataset based the label (column item) without taking into account the case [First criterion – no change] and in case of equality I’ll then sort the rows based on the price (descending) [second criterion to be introduced].

To test this, I’m introducing a few changes in my dataset. I’m adding a tenth record with the item Fishing Rod and updating the price for all the Fishing Rod and Bait items.

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 = 4.25],
      [OrderID=8, CustomerID=5, Item = "Fishing Rod", Price = 120.0],
      [OrderID=9, CustomerID=6, Item = "Bait", Price = 3.25],
      [OrderID=10, CustomerID=5, Item = "Fishing Rod", Price = 80.0]
    }
  ),
#"Changed Type" =
  Table.TransformColumnTypes(
    Source,
    {
      {"Item", type text},
      {"Price", type number}
    }
  ),

The basic principle for sorting based on you own criteria has not changed and you need to specify a list of criterion as the second argument of the Table.Sort function.

#"Sorted Rows" =
  Table.Sort(
    #"Changed Type",
    {
       each Text.Upper([Item]),
       each [Price]*-1
    }
  )

and this method definitely gives the expected result:
sort-table-multiple-criteria

Advertisements

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