Power Query系列 - 排序Ranking

时间:2024-02-01 20:41:29

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中。

(完)