powerquery分组_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,{"顺序排名", "标准竞赛排名", "修改竞争排名", "密集排名", "分数排名"}),

#"AddedIndex" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1)in#"AddedIndex"

结果如下：

Avitago儿童蜂胶和同仁堂蜂蜜在列表中排名第2和第3，但销售均为400，而默认按照字母顺序，分别获得第2和第3的排名，而Aurinda 澳琳达按销售排第4。

当然，可以加入相同销售额的时候按照名字倒叙排名，同仁堂排第2，Avitago排第3。

排名方法2：标准比赛排名 (1224 排序)(Standard Competition Rank)

这种排名方法涉及使用一些分组以使值正确显示：

按降序对“销售”列进行排序

添加索引列，从1开始

转到转换->组

按销售列分组

创建以下列：

使用“索引”列上的“最小”运算的排名

使用“所有行”操作的数据

展开项目列

根据需要重新排列列

let

Source=SalesData,

#"AddedIndex" = 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 Competi