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.
我想根据这些值按时间顺序对大量行进行排序。在Excel中,可以按自定义列表进行排序,我只需输入我希望对项目进行排序的顺序。
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.
我在Power Query中需要相同的功能,但我还没有想出如何做到这一点。我只能将排序顺序设置为Order.Ascending或Order.Descending。
Is there a good way to implement sort-by-custom-list in Power Query?
有没有一种在Power Query中实现按自定义排序列表的好方法?
3 个解决方案
#1
4
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的第二个参数也可以是一个函数:一个函数,它接受两行并返回它们之间的排序(la strcmp)或一个函数,它接受一行并返回应该用于比较的值。因此,对您描述的值进行排序的一种方法就是说
= Table.Sort(Table, each List.PositionOf({"Fall 12-13", "Winter 12-13", "Spring 12-13", "Fall 13-14"}, [Column]))
#2
1
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.
创建一个包含两个字段的单独表:Season和SortOrder。季节是您的文本,SortOrder将是一个表示订单的整数。然后在Season上将现有表连接到此表,包括SortOrder列,并对其进行排序。
#3
0
I don't have enough reputation to address Ron Rosenfeld's question directly, but here's the answer:
我没有足够的声誉来直接解决Ron Rosenfeld的问题,但这里的答案是:
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.
您也可以将第二个参数实现为函数列表。列将按照您在列表中提供的顺序进行排序。例如,以下示例中的Column,Column1,Column2。
= 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}})
#1
4
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的第二个参数也可以是一个函数:一个函数,它接受两行并返回它们之间的排序(la strcmp)或一个函数,它接受一行并返回应该用于比较的值。因此,对您描述的值进行排序的一种方法就是说
= Table.Sort(Table, each List.PositionOf({"Fall 12-13", "Winter 12-13", "Spring 12-13", "Fall 13-14"}, [Column]))
#2
1
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.
创建一个包含两个字段的单独表:Season和SortOrder。季节是您的文本,SortOrder将是一个表示订单的整数。然后在Season上将现有表连接到此表,包括SortOrder列,并对其进行排序。
#3
0
I don't have enough reputation to address Ron Rosenfeld's question directly, but here's the answer:
我没有足够的声誉来直接解决Ron Rosenfeld的问题,但这里的答案是:
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.
您也可以将第二个参数实现为函数列表。列将按照您在列表中提供的顺序进行排序。例如,以下示例中的Column,Column1,Column2。
= 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}})