Power Query: sort by custom list

Question

I have a list of time seasons within school years:

"Fall 12-13",
"Winter 12-13",
"Spring 12-13",
"Fall 13-14",
etc.

I want to sort a large number of rows chronologically based on these values. In Excel it is possible to sort by a custom list where I simply input the order that I want the items to be sorted by.

I need that same functionality in Power Query but I have not yet figured out how to do this. I have only been able to set sort order to Order.Ascending or Order.Descending.

Is there a good way to implement sort-by-custom-list in Power Query?

Answer 1

The second argument to Table.Sort can also be a function: either a function that takes two rows and returns an ordering between them (a la strcmp) or a function that takes a single row and returns the value that should be used for comparisons. So one way to sort the values you describe would be to say

= Table.Sort(Table, each List.PositionOf({"Fall 12-13", "Winter 12-13", "Spring 12-13", "Fall 13-14"}, [Column]))

Answer 2

Make a separate table with two fields: Season and SortOrder. Season is your text and SortOrder will be an integer indicating the order. Then join your existing table to this one on Season, include the SortOrder column, and sort on it.

Answer 3

I don't have enough reputation to address Ron Rosenfeld's question directly, but here's the answer:

You can implement the second argument as a list of functions as well. The columns will be sorted in the order you provide them in the list. E.g Column, Column1, Column2 in the example below.

= Table.Sort(Table,{ each List.PositionOf({"Fall 12-13", "Winter 12-13", "Spring 12-13", "Fall 13-14"}, [Column]), {"Column1",Order.Ascending}, {"Column2",Order.Ascending}})