Log In
Register
Home
May 6, 2022
Offline
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.
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
Perhaps the link below helps you to get started with Dense Ranking in PQ.
May 6, 2022
Offline
@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.
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
Can you upload a file with some anonymized data that resembles your real data and indicate how you would dense-rank it manually?
May 6, 2022
Offline
@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 )
)
1 Guest(s)
