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. However, thanks to someone in this platform, that gave a good and quick way of solving it. He supplied the following custom function to handle this case as discussed in this trend:How To Effectively Dense Rank Groups in a Table Using Powerquery i discovered that in his function, some column values are hardcoded.So if i got several column values,i will need to manually add them.How do handle this problem effectively?
| Scores | ID | Class | Arabic | Maths | Rank |
|---|---|---|---|---|---|
| 130 | 1002 | SS1 | 30 | 100 | |
| 180 | 2003 | SS2 | 100 | 80 | |
| 140 | 1006 | SS1 | 90 | 50 | |
| 130 | 1007 | SS1 | 80 | 50 | |
| 200 | 3002 | SS3 | 100 | 100 | |
| 200 | 3010 | SS3 | 100 | 100 | |
| 70 | 3010 | SS3 | 50 | 20 |
Expected results
| Scores | ID | Class | Arabic | Maths | Rank |
|---|---|---|---|---|---|
| 130 | 1002 | SS1 | 30 | 100 | 2nd |
| 180 | 2003 | SS2 | 100 | 80 | 1st |
| 140 | 1006 | SS1 | 90 | 50 | 1st |
| 130 | 1007 | SS1 | 80 | 50 | 2nd |
| 200 | 3002 | SS3 | 100 | 100 | 1st |
| 200 | 3010 | SS3 | 100 | 100 | 1st |
| 70 | 3010 | SS3 | 50 | 20 | 3rd |
CodePudding user response：
How about this which ranks the Scores column after grouping on Class, working for any number of columns
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), //optional
#"Grouped Rows" = Table.Group(#"Added Index", {"Class"}, {{"data", each rank(_,"Scores","Rank"), type table }}),
ColumnsToExpand = List.Difference(List.Distinct(List.Combine(List.Transform(Table.Column(#"Grouped Rows", "data"), each if _ is table then Table.ColumnNames(_) else {}))),{"Class"}),
DynamicExpanded = Table.ExpandTableColumn(#"Grouped Rows", "data",ColumnsToExpand ,ColumnsToExpand ),
#"Sorted Rows" = Table.Sort(DynamicExpanded,{{"Index", Order.Ascending}}), // optional
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}) // optional
in #"Removed Columns"
using this function, named rank
(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.Descending}}),
#"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")),
#"Sorted Rows2" = Table.Buffer(Table.Sort(#"Expanded data",{{"RankIndex", Order.Ascending}})), // optional
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows2",{"RankIndex"})
in #"Removed Columns1"