Power Query系列 - 排序Ranking
Power Query系列 - 排序Ranking
难度： ★★☆☆☆（1星）
适用范围： ★★★☆☆（3星）
概况：
在数据分析中，我们常常需要对数据进行排序，同时我们想知道某个项目或者产品的排名，以方便查阅或对排名靠前的进行特别处理。
应用场景：
以下是几个应用场景：
- 对前五名销售进行奖励
- 对前五名的销售求和
- 对末3位进行淘汰
- ....
然鹅，排名有多种，下面是小抄：
- 顺序排序（1234排序）-这种排序方法对数据的每一行使用序号，而不关心平局/并列。
- 标准比赛排名（1224排序）-也称为跳过排名的一种形式，该方法为平局分配相同的排名，并且跳過下一個排名值。 在这种情况下，我们的值分别变为1,2,2,4,5 。
- 修改后的比赛排名（1334排序）-与标准比赛排名方法类似，但是跳过的值位于平局之前 。 在这种情况下，我们将得到1,3,3,4,5
- 密集排名（1223排序）-在这种排名方法中，平局/并列（一样的值）具有相同的排名，但不会跳过下一个值。 在这种情况下，我们有1,2,2,3,4,5。
- 分数排名（1/2.5/2.5/4排序）-这个排名我还没有见过或用过。
那么，如何在Power Query中进行构建，以便可以在Excel和Power BI和中显示它们呢？
最终Excel效果显示如下：
欢迎转载，请保留原文链接和作者信息。O(∩_∩)O谢谢。
Power Query系列 - 排序Ranking
作者：马丁叔叔
链接：http://www.cnblogs.com/lizardbi/p/PowerQuery-POWERBI-Ranking.html
数据模型：
要点：
- 何时加入索引Index
- 用什么分组Grouping方法
一起来实现各种排名方法吧！
如果下载附件，您将看到它具有上面显示的完整的Excel表格。 为了简化说明，以下步骤只选取了两列作为SalesData表：
在Excel表格中选择一个单元格->数据->从表格/范围
选择项目和销售列->右键单击->删除其他列
仅作为连接加载
这给了我一个简单的表，其中仅包含产品名称和值，如下所示：
| 產品 | 销售 |
|---|---|
| Comvita 康维他 | 500 |
| Avitago儿童蜂胶 | 400 |
| 同仁堂蜂蜜 | 400 |
| Aurinda 澳琳达 | 200 |
| By-Health 汤臣倍健 | 100 |
如您所见，销售列已经按降序排序，这是我们对并列排名的关键所在。
对于每种排名方法-我们实际上将通过以下方式开始每个新查询(Query)：
引用SalesData查询 (Reference)
重命名新查询以表示所展示的排名方法
这意味着我将基于上面的视图每次给出步骤，因为这是我们从引用步骤中应该得到的。
排名方法1：顺序排名（1234排序） (Ordinal Rank)
这种排名方法非常容易创建：
按降序对“销售”列进行排序
按升序对“项目”列进行排序（按字母顺序对关系进行排名）
转到添加列->索引列->从1开始
重命名索引列为排名
根据需要重新排列列
对，就酱。 它只是向您的数据排序方式添加了一个行号，如下所示：
let Source = SalesData, #"Removed Columns" = Table.RemoveColumns(Source,{"顺序排名", "标准竞赛排名", "修改竞争排名", "密集排名", "分数排名"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1) in #"Added Index"
结果如下：
Avitago儿童蜂胶和同仁堂蜂蜜在列表中排名第2和第3，但销售均为400，而默认按照字母顺序，分别获得第2和第3的排名，而Aurinda 澳琳达按销售排第4。
当然，可以加入相同销售额的时候按照名字倒叙排名，同仁堂排第2，Avitago排第3。
排名方法2：标准比赛排名 （1224 排序）(Standard Competition Rank)
这种排名方法涉及使用一些分组以使值正确显示：
按降序对“销售”列进行排序
添加索引列，从1开始
转到转换->组
按销售列分组
创建以下列：
使用“索引”列上的“最小”运算的排名
使用“所有行”操作的数据
展开项目列
根据需要重新排列列
let Source = SalesData, #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1), #"Grouped Rows" = Table.Group(#"Added Index", {"销售"}, {{"Rank", each List.Min([Index]), type number}, {"Data", each _, type table}}), #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"產品"}, {"產品"}), #"Reordered Columns" = Table.ReorderColumns(#"Expanded Data",{"產品", "销售", "Rank"}) in #"Reordered Columns"
结果如下：
结果正确地表明，Avitago儿童蜂胶和同仁堂蜂蜜在列表中排名第2和第3，但销售均为400，故此，分别获得第2的排名，而Aurinda 澳琳达（在列表中排名第4）的排名为4，因为并列第二名，故没有第三名。
排名方法3：修改后的比赛排名 （1334排序）（Modified Competition Rank）
要按照修改后的比赛排名方法创建排名，我们需要：
按降序对“销售”列进行排序
添加索引列，从1开始
转到转换->组
按销售列分组
创建以下列：
使用“索引”列上的“最大”运算的排名
使用“所有行”操作的数据
展开项目列
根据需要重新排列列
let Source = SalesData, #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1), #"Grouped Rows" = Table.Group(#"Added Index", {"销售"}, {{"Rank", each List.Max([Index]), type number}, {"Data", each _, type table}}), #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"產品"}, {"產品"}), #"Reordered Columns" = Table.ReorderColumns(#"Expanded Data",{"產品", "销售", "Rank"}) in #"Reordered Columns"
此排名方法与标准竞争排名之间的唯一区别是，我们使用“索引”列的“最大值”而不是先前方法中使用的“最小值”来创建“排名”列。
结果表明，Avitago儿童蜂胶和同仁堂蜂蜜在列表中排名第2和第3，但销售均为400，故此，分别获得第3的排名，而Aurinda 澳琳达（在列表中排名第4）的排名为4。因为并列第三名，故没有第二名。
排名方法4：密集排名（1223排序）（Dense Rank）
密集排名方法要求更改标准竞争排名方法中步骤的顺序。 也就是说，Group By命令必须在添加Index列之前出现：
按降序对“销售”列进行排序
转到转换->组
按销售列分组
创建以下列：
使用“所有行”操作的数据
添加索引列，从1开始
展开项目列
根据需要重新排列列
let Source = SalesData, #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1), #"Grouped Rows" = Table.Group(#"Added Index", {"销售"}, {{"Data", each _, type table}}), #"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1), #"Expanded Data" = Table.ExpandTableColumn(#"Added Index1", "Data", {"產品"}, {"產品"}), #"Reordered Columns" = Table.ReorderColumns(#"Expanded Data",{"產品", "销售", "Index"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Index", "Rank"}}) in #"Renamed Columns"
此方法将产生以下结果：
结果正确地表明，Avitago儿童蜂胶和同仁堂蜂蜜在列表中排名第2和第3，但销售均为400，
故此，分别获得第3的排名，而Aurinda 澳琳达（在列表中排名第4）的排名为3。
这里与竞赛排名和修改后竞赛排名不同之处在于，
这里不会跳过任何一位的排名数字。
排名方法5：分数排名 (Fractional Rank)
就像我一开始提到的那样，我发现这是最奇怪的排名方法之一。
但是，像上面其他的一样，创建它非常容易。
按降序对“销售”列进行排序
添加索引列，从1开始
转到转换->组
按销售列分组
创建以下列：
使用“索引”列上的“平均”运算的排名
使用“所有行”操作的数据
展开项目列
根据需要重新排列列
let Source = SalesData, #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1), #"Grouped Rows" = Table.Group(#"Added Index", {"销售"}, {{"Rank", each List.Average([Index]), type number}, {"Data", each _, type table}}), #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"產品"}, {"產品"}), #"Reordered Columns" = Table.ReorderColumns(#"Expanded Data",{"產品", "销售", "Rank"}) in #"Reordered Columns"
结果如下:
这个，你在哪里用到过……？
总结
实际上，可以看到，仅对应用分组Grouping方法时所选择的步骤顺序，并且/或者，汇总时进行一些小的修改即可轻松地更改排名方法：
譬如，有的先加入索引列，有的后加；有的用最大值，有的用最小值，有的用平均值。
但是，
如果你熟悉了这些，任何排序方法都难不倒你了！！
当然我们可以使用Excel公式创建每种排名方法，
但，文中的方法用Power Query实现，
酱，我们可以将结果直接加载到Power Pivot或者Power BI中。
（完）
