How To Effectively Dense Rank Groups in a Table Using Powerquery

val 0
Viewed 94

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.

Scores ID Class Rank
130 1002 SS1
180 2003 SS2
140 1006 SS1
130 1007 SS1
200 3002 SS3
200 3010 SS3

Expected results

Scores ID Class Rank
130 1002 SS1 2nd
180 2003 SS2 1st
140 1006 SS1 1st
130 1007 SS1 2nd
200 3002 SS3 1st
200 3010 SS3 1st
100 3007 SS3 2nd
80 3045 SS3 3rd

The challenge is how do I achieve this using powerquery.

powerquery excel
1 Answers
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"

enter image description here

Notes:

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

Edit:
if Table.AddRankColumn not available

  • Add Custom function as blank query
  • Rename the blank query per the comments
//Rename fnRankDense

(t as table)=>

let 
    tbl = Table.Buffer(t),

//Group by Scores
    group = Table.Group(tbl, {"Scores"}, {
        {"grouped", each _, type table[ID=Int64.Type, Class=text, Index=Int64.Type]}
    }),

//Sort by Scores descending
    sort = Table.Sort(group,{"Scores", Order.Descending}),

//add index column for ranking
    #"Add Rank Column" = Table.AddIndexColumn(sort,"Rank",1),

//Expand the table
    #"Expanded Rank" = Table.ExpandTableColumn(#"Add Rank Column", "grouped", {"ID", "Class", "Index"})

in 
    #"Expanded Rank"

New M Code
to use with custom function

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
//   Aggregate using custom function to generate dense rank scores
    #"Grouped Rows" = Table.Group(#"Added Index", {"Class"}, {{"all", 
        each fnRankDense(_), type table [Scores=nullable number, ID=nullable number, Class=nullable text, Index=number, Rank=number]}}),

//Expand the grouped columns and set in desired order
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Scores", "ID", "Index", "Rank"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded all",{"Scores", "ID", "Class", "Index", "Rank"}),

//Sort rows back to original order and delete index column
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"
Ron Rosenfeld0
Related