Power查询中的秩函数

Question

需要RANK函数在Power Query中，就像Excel，SQL，DAX.

目前最好的答案是：

自Jun-2022

• Solution with the best performance，以来，至少在我的测试中，

1. 内置了Table.AddRankColumn函数(比构建
2. 要好10%)

Answer 1

MS最近在M中发布了一个秩函数。

这满足了所有的要求，如领带，密集等级等。

https://learn.microsoft.com/en-us/powerquery-m/table-addrankcolumn

Answer 2

请试试这个：

(tblSource as table, clmValues as text, optional RankColumnName as nullable text) =>
let    
    doRename = Table.RenameColumns(tblSource, {{clmValues , "XXXX"}}),    
    AddRank = Table.AddColumn(
        doRename,
        if RankColumnName = null  then "Rank"  else RankColumnName,
        each Table.RowCount(
            Table.SelectRows(doRename, (s)=> s[XXXX]>[XXXX])   // Magic ;)
        )+1,
        Int64.Type
    ),
    unRename = Table.RenameColumns(AddRank, {{"XXXX", clmValues}})
    
    // Regarding doRename, unRename steps - NB! it's superSmart, NOT superStupid (:
    // ... at least, I don't know how to avoid it ...
        
in
    unRename

它的工作原理：

1. ​

1. ​

1. ​

1. ​

Answer 3

这就是我在PowerQuery中使用的排名。它适用于分组，而不是对每一行进行迭代的Table.RowCount，因此我的想法是，对于大型数据集来说，这样做要快得多。

(Source as table, RankColumnName as text, optional OutputName as nullable text) =>
let
#"Added Index" = Table.AddIndexColumn(Source, "RankIndex", 0, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index", RankColumnName, {{"data", each _, type table}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{RankColumnName, Order.Ascending}}),
#"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", if OutputName=null or OutputName="" then"Rank" else OutputName, 1, 1, Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Index1",RankColumnName),
#"Expanded data" = Table.ExpandTableColumn(#"Removed Columns", "data", Table.ColumnNames(#"Added Index"), Table.ColumnNames(#"Added Index")), //next row optional
#"Sorted Rows2" = Table.Buffer(Table.Sort(#"Expanded data",{{"RankIndex", Order.Ascending}})),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows2",{"RankIndex"})
in #"Removed Columns1"

​

​

如果您需要一个SQL答案，您可能应该用它来标记这个问题。