Table.AddRankColumn(#"Added Index", "Rank", {"Scores", Order.Descending}, [RankKind = RankKind.Dense])
根据您的版本,您可能需要将其添加到 M 代码中,或者它可能会被视为 Power Query 菜单栏中的一个选项。
另请注意,结果还将按排名顺序对表进行排序。因此,您可能需要添加一个索引列才能将其排序回原始顺序。
这是使用您的数据的示例:
在添加排名列之前将代码编辑为按类别分组
let
//Read in the original table
Source = Excel.CurrentWorkbook(){[Name="Scores"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Scores", Int64.Type}, {"ID", Int64.Type}, {"Class", type text}}),
//add Index column to be able to return to original order
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
//Group by class
// then add rank column to each subtable
#"Grouped Rows" = Table.Group(#"Added Index", {"Class"}, {
{"Rank", each Table.AddRankColumn(_, "Rank", {"Scores", Order.Descending}, [RankKind=RankKind.Dense]) }}),
//expand the grouped tables
#"Expanded Rank" = Table.ExpandTableColumn(#"Grouped Rows", "Rank", {"Scores", "ID", "Index", "Rank"}),
//Sort back to original order
#"Sorted Rows" = Table.Sort(#"Expanded Rank",{{"Index", Order.Ascending}}),
//remove Index column and re-order the columns as desired
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Scores", "ID", "Class", "Rank"})
in
#"Reordered Columns"
Notes:
- 您可以轻松编写自定义函数来将排名输出为序数与基数。
Edit:
if Table.AddRankColumn
无法使用
- 添加自定义函数作为空白查询
- 根据评论重命名空白查询
//Rename fnRankDense
(t as table)=>
let
tbl = Table.Buffer(t),
//Group by Scores
group = Table.Group(tbl, {"Scores"}, {
{"grouped", each _, type table[ID=Int64.Type, Class=text, Index=Int64.Type]}
}),
//Sort by Scores descending
sort = Table.Sort(group,{"Scores", Order.Descending}),
//add index column for ranking
#"Add Rank Column" = Table.AddIndexColumn(sort,"Rank",1),
//Expand the table
#"Expanded Rank" = Table.ExpandTableColumn(#"Add Rank Column", "grouped", {"ID", "Class", "Index"})
in
#"Expanded Rank"
新M代码
与自定义函数一起使用
let
//Read in the original table
Source = Excel.CurrentWorkbook(){[Name="Scores"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Scores", Int64.Type}, {"ID", Int64.Type}, {"Class", type text}}),
//add Index column to be able to return to original order
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
//Group by class
// Aggregate using custom function to generate dense rank scores
#"Grouped Rows" = Table.Group(#"Added Index", {"Class"}, {{"all",
each fnRankDense(_), type table [Scores=nullable number, ID=nullable number, Class=nullable text, Index=number, Rank=number]}}),
//Expand the grouped columns and set in desired order
#"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Scores", "ID", "Index", "Rank"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded all",{"Scores", "ID", "Class", "Index", "Rank"}),
//Sort rows back to original order and delete index column
#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
#"Removed Columns"