Filtering Users based on exam results

[JonBonesJones]

Hello, good morning everyone.

I'm really struggling with an issue and wondered if a solution is possible or not? Apologies if I can't post too many examples, as my data contains students data and work-related data. 

Essentially, each student must pass 5 exams over a time period and these are inserted into a Sharepoint list as either a 1 or 0. Each of these corresponds to a different value, which total a value of 1 or 100% - E.g - Exam1 0.1, Exam2 0.2, Exam3 0.3, Exam4 0.2, Exam5 0.2. An example is below -

Example of how data is inputted

This is sorted within the Power Query side of things but where it get's slightly complicated is representing this data as students can submit certain exams multiple times (For training or practice for example). This means that some users may have multiple records for the same exam. 

In an ideal world, I'd like to represent each students results within a table but I'm not sure on how to only show distinct values for each exam and user. I was thinking of having a table with student name and then simply a column for each exam and showing if they had been completed or not but due to some users having multiple results for the same exam, I'm sturggling to represent this data correctly. I wondered if anyone had any ideas regarding this? Thank you!

I'll try and answer any questions if I can 🙂

[JonBonesJones]

Hello Joao! Thank you for your reply, that was very in-depth and thank you for putting the effort into answering my question! I'm going to try and use some of those elements in my solution - It might need a slight bit of tinkering from my side, simply with how the data is stored, but you've been more than helpful! I'll make a start on it today and see how I get on. Thank you for your help! 🙂

[JoaoMarcelino]

Hi @JonBonesJones ,

It was my pleasure, I'm glad to help 🙂

[JoaoMarcelino · Accepted solution]

Hi @JonBonesJones 🙂

The table/query that stores the exams is the facts table, as such, it probably has a date column and a student's name column.
From what I can understand, students can practice the exams, but you only want the last value to be considered (valid exam).

It's a bit hard to help more without looking at the whole data, but you might want to perform the following ( you can always go back and try another way if this doesn't work):

1. Duplicate that query just in case you need to have the original backed up;

2. I tried to find a way to sample your data structure:

3. Try to find a better way to deal with the data: let's try unpivoting the table
Choose the 2 columns you want to keep and click at "unpivot others"

The result will be:

Note that you can rename the columns, for example, as Tests and Grades.

4. Unload the original query( you have the backup saved)

5. Make 2 references to the transformed query

You can call to one "table max" and the other "table".

6. Go to "table max" and perform a Groupby of "Student" and "Test" by the max of date
(this will allow you to consider the last exam - it should be the valid one - by student and type of exam)

On this step, where it says "New column name" (Count), you can rename it to "Date"
Click "ok" at the end.

7. Merge your new query with the query Table max

Make sure the keys match

Click "ok"

8. Click on "Expand" (black circle) and expand only the grades

This should give you the grades that correspond to the maximum (last date) in which a student answered an exam, by exam 🙂

I'll leave the sample file attached!

Hope I was of assistance!
Cheers
Joao Marcelino

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!