RANK function in/for Power Query-CodePudding

Need RANK function in Power Query, Like in Excel, SQL, DAX...

The Best answer for now

CodePudding user response：

This is what I use as rank in PowerQuery. It works on grouping instead of Table.RowCount iterated over each row, so my thought is it would be much faster for large data sets

(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"

if you need a SQL answer you should probably tag the question with that

CodePudding user response：

MS recently released a rank function in M.

This satisfies all requirements like ties, dense rank etc.

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