Good All, I have Dataset of student records.
Dataset 1 is saved as Book1.xls
Dataset 2 is saved as Book2.xls
Dataset 1 , saved as Book1.xls has records of students whose classes represent their various groups of learning formation.The students are graded and their Total score is stored in a Column named "Total".
Ques1: How do i Dense rank the students based on the Total score considering their various classes.Each classes, SS1, SS2 SS3, are different groups of academic formation. If there is a tie, i what to give them same rank based on their classes with no skip.
Dataset 2 is saved as Book2.xls: This workbook has similar structure of book1.xls. Except location was added, to take into consideration various location of students or Students enrollment location.
Ques2: How do i Dense rank the students based on their Classes and Location as well. For instance,the student in SS1 was enrolled in Abba, another student in SS1 was enrolled in ZENSCO, others in FINIDA, How do i rank them based on their location and classes?
N.B: i am aware there is a new function for ranking, called:Table.AddRankColumn, buh it is not yet available for some excel version.How can one effectively use a custom function to handle this challenge.
Thank you.
Perhaps the link below helps you to get started with Dense Ranking in PQ.
https://www.myonlinetraininghub.com/dense-ranking-in-power-query
@Riny van Eekelen, thank you. i have seen the article on this platform, buh it doesnt meet my need. The location, i posted wasnt addressed.It involves splitting my large tables , already my queries logs as gotten merge and splitting of tables already,in previous steps.I was seeking for moderate function to handle this task and save powerquery memory.More so, these data are data coming from the web.
Can you upload a file with some anonymized data that resembles your real data and indicate how you would dense-rank it manually?
@Riny Van, thank you. I discover that one can switch to "DAX OPTION" to resolve this:
Modelling --> New Table
New Table =
ADDCOLUMNS (
YourTableName,
"Ranks", RANKX ( YourTableName, YourTableName[Scores ],, DESC, DENSE )
)
Great! I'm not into DAX so much myself, so I didn't know.
