RANK function in/for Power Query

Please try this:

(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

How it works:

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

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