Ranking of scores with ties in powerquery

val 0
Viewed 61

Good day all,

I wish to rank the scores column such that the highest number take 1st position=1st. The second largest number takes the second position etc. Where there are ties ,give them same ranking order.say scores=140 occurring twice is placed 3rd.So in this case the scorers will have the same ranking

Scores B2 B4 Rank
130 A2 A1
180 B3
140 A2 2
130 A2 A1
200 A1
140 A2 2

The challenge is how do I achieve this using powerquery.

Thank you.

powerquery excel
2 Answers

Use the Table.AddRankColumn function with RankKind.Competition.

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

enter image description here

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ2UFDSUXI0ApOGCkqxOkBBC7CgkzGIhIiYICkzgqrCptXIwADGxaU1FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Scores = _t, B2 = _t, B4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Scores", Int64.Type}, {"B2", type text}, {"B4", type text}}),
    Rank = Table.AddRankColumn(#"Changed Type","Rank",{"Scores", Order.Descending})
    
    in Rank
userjpYe10

DAX option :

Modelling --> New Table

New Table =
ADDCOLUMNS (
    YourTableName,
    "Ranks", RANKX ( YourTableName, YourTableName[Scores ],, DESC, DENSE )
)

or simply Add New Column

Rank Column = RANKX(YourTableName
                        ,YourTableName[Scores ],,DESC,DENSE)
Umut K0
Related