September 5, 2018 at 5:22 pm #10509
I have what may be kind of a complex issue.
I’m currently working with a data set in power query and wondering whether there was any way (within either power query, if not perhaps with a DAX formula) to create a flag that will place students in one of three categories based on the order in which they completed a test. I would have to take into consideration the date as well as time (so if a student completed an array of tests on the same date, the time would determine into which category they fell)
I have attached a sample workbook with anonymized data as well as the desired result which will (hopefully) be a clearer reference.
Any suggestions would be greatly appreciated!
DarkoSeptember 5, 2018 at 10:15 pm #10511
ID 1 and ID 4 have the same sequence (Eng, PTD, Mat), but different groups, why?
In your workbook there are 3 exams, but your example only gives the order of 3 sequences. What about PTP, Mat, Eng; Mat, Eng, PTP; Mat, PTD, Eng.
Would it be impossible to have taken any one of the exams more than once? If so, how is that enforced?
Would it be possible to have taken less than three exams? If so, what then?
What version of Excel are you using?
TomSeptember 6, 2018 at 1:51 pm #10515
I’m currently using office 365 (2016).
Attached is an updated version of my working file. I believe I mistakenly uploaded the wrong file. My apologies!September 6, 2018 at 2:25 pm #10517
Also, it would not be possible to have taken one of the exams more than once. It would be enforced in the data cleaning step prior to this one.
It would be possible to have taken fewer than 3 exams.
So if a student completed just V and Q, I would want to categorize them in “Group 1”, if they’ve only completed V and NV, “Group 2”, only Q and V “Group 3”, only Q and NV “Group 4”, NV and V “Group 5” and NV and Q “Group 6”.
If they have only completed one test, I would want to return a “missing” label.
Thank you again for all the help. Hope this clarifies a little more.September 6, 2018 at 2:50 pm #10519
Would a blank be suitable for a “missing” label or would you want a phrase like “missing exam”?September 6, 2018 at 3:24 pm #10520
I’ve attached a final, comprehensive worksheet that includes my working set and desired result. I’ve put the possible permutations of categories in table form as well as some of the exceptions that might occur.
Essentially I would like to have 6 primary groupings that will occur most frequently in the data (Group 1 – Completed V, then Q, then NV; Group 2 – Completed V, then NV then Q; Group 3 – Completed Q, then V then NV; Group 4 – Completed Q, then NV then V; Group 5 – Completed NV, then V then Q; and Group 6 – Completed NV, then Q then V).
I would also like to identify those that have completed only two of the three, or one of the three and label which those were.
Thank you againSeptember 6, 2018 at 3:40 pm #10524
And someone who would have only completed 2 of the tests would fall into one of the 6 categories.
For example, if a case is recorded as having completed V, then NV, they would fall into group 2, as we know that the only next possible test they will take is going to be Q. The same follows for the other 5 possible groupings.
Hopefully this isn’t too convoluted.September 6, 2018 at 9:36 pm #10528
Please see the pivot on worksheet “Desired Result (Grouping)” in the attached workbookSeptember 7, 2018 at 1:32 pm #10539
This is fantastic. Thank you so much tomallan.
And thank you for all the help with previous posts.
J.September 7, 2018 at 9:57 pm #10542
I’ve just run into another issue. I’m trying to replicate your solution, but I’m getting all blanks when I insert the calculated column “Test Group” in powerpivot. The DAX/column referencing seems to be lining up, but not sure what else I could be doing wrong.
I’ve attached a sample of where I’m hitting a bit of a wall.September 8, 2018 at 2:45 am #10545
When you look at the tables in diagram view, they are missing the relationship between ID columns.
Once the relationship was added, column was populated.September 9, 2018 at 2:31 pm #10553
Got it! Thank you so much for explanation and helping to figure this out.
You must be logged in to reply to this topic.