I have been trying the easiest method to dense rank the following data for data having groups or category. I have ask similiar question for ranking a data, but this one is for data with grouping.

I wish to rank the scores column as shown below, 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 score=200 of ID=3002, ID=3010 occurring twice is placed 1st for the class SS3. So in this case the scorers will have the same ranking => Dense Ranking. same thing goes for all the classes.

The Class represent group of formations. Each individual, would be graded per each class. All methods have google, seems cumbersome and somehow takes more of powerquery memory space.

Expected results

The challenge is how do I achieve this using powerquery.

CodePudding user response：

Table.AddRankColumn(#"Added Index", "Rank", {"Scores", Order.Descending}, [RankKind = RankKind.Dense])

Depending on your version, you may need to add this in M Code, or it may be seen as an option in the Power Query menu bar.

Also note that the result will be to also sort the table in rank order. So you may need to add an index column to be able to sort it back to your original order.

Here is an example using your data:

Code edited to Group by Class before adding the Rank column

let

//Read in the original table
    Source = Excel.CurrentWorkbook(){[Name="Scores"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Scores", Int64.Type}, {"ID", Int64.Type}, {"Class", type text}}),

//add Index column to be able to return to original order
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

//Group by class
//  then add rank column to each subtable
    #"Grouped Rows" = Table.Group(#"Added Index", {"Class"}, {
        {"Rank", each Table.AddRankColumn(_, "Rank", {"Scores", Order.Descending}, [RankKind=RankKind.Dense]) }}),

//expand the grouped tables
    #"Expanded Rank" = Table.ExpandTableColumn(#"Grouped Rows", "Rank", {"Scores", "ID", "Index", "Rank"}),

//Sort back to original order
    #"Sorted Rows" = Table.Sort(#"Expanded Rank",{{"Index", Order.Ascending}}),

//remove Index column and re-order the columns as desired
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Scores", "ID", "Class", "Rank"})
in
    #"Reordered Columns"

Notes:

• You can easily write a custom function to output the rank as ordinal vs cardinal numbers.