CODEWITHSUNDEEP
excelsortingpowerquery
zacksg1
zacksg1

Reputation: 101

Power Query: sort by custom list

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?

Upvotes: 9

Views: 16254

Answers (3)

CMatthews
CMatthews

Reputation: 71

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}})

Upvotes: 7

Curt Hagenlocher
Curt Hagenlocher

Reputation: 20916

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]))

Upvotes: 15

Dick Kusleika
Dick Kusleika

Reputation: 33145

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.

Upvotes: 3

Related Questions