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

The challenge is how do I achieve this using powerquery.

Thank you.

CodePudding user response：

Use the Table.AddRankColumn function with RankKind.Competition.

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

CodePudding user response：

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)