Evaluation Scenario - Power Query

[goko]

Hi,
Thanks for reading this and double thanks if you give me a hint.

I have Student Exam Records Sheet with following columns.

Columns	Desc.
School	Multiple Schools
Enrollment No	Is unique across all schools
Student Name
Course Code	There are multiple [Course Code], but a student can enroll in only one
Semester Name	Each course [Course Code] can have multiple Semester Name per course code but every course has a fixed number of Semesters with same name
Exam Name	Each Semester [Semester Name] can have multiple Exams [Exam Name] and this can be different for diff Course
Exam Date
Total Marks
Marks Obtained
Exam Status	>=40% is pass else fail

I'm creating a Final Status sheet to check if a student has cleared all exams [Exam Name] of each [Semester Name] and
- if a particular exam record is missing or
- the student failed

I want to have a Table like following

School	Enrollment No	Course Code	Sem. 1	Sem. 2	Sem.3	Sem.4
x	123​	2000​	ok	fail in r1, missing t1	ok	missing t1
y	112​	2003​	ok	ok	ok	missing x1, fail in p1

How I'm getting by at the moment (with out power query)

1. I'll separate student by [Course Code] and copy School, Enrollment No to separate sheet
2. I'll create columns for each semester e.g. Sem.1, Sem.2 etc.
3. I'm going create some combination of multiple if & countifs, where i'll be checking each criteria and returning either 1, 0 or error message if the some exam is not found and concatenate it all

XL2BB [Exam Record] MiniSheet Code Below

Scenario-2.xlsx
	A	B	C	D	E	F	G	H	I	J
1	School	Enrollment No	Student Name	Course Code	Semester Name	Exam Name	Exam Date	Total Marks	Marks Obtained	Exam Status
2	South	111	Cristina Alvarez	2012	Sem.1	R1	########	50	30	pass
3	South	111	Cristina Alvarez	2012	Sem.1	T3	########	20	13	pass
4	South	111	Cristina Alvarez	2012	Sem.1	T2	########	20	13	pass
5	South	111	Cristina Alvarez	2012	Sem.1	Final	########	50	24	pass
6	South	111	Cristina Alvarez	2012	Sem.1	PROJECT	########	100	58	pass
7	South	111	Cristina Alvarez	2012	Sem.1	T1	########	20	13	pass
8	South	111	Cristina Alvarez	2012	Sem.2	T2	########	20	9	pass
9	South	111	Cristina Alvarez	2012	Sem.2	T3	########	20	13	pass
10	South	111	Cristina Alvarez	2012	Sem.2	R1	########	50	45	pass
11	South	111	Cristina Alvarez	2012	Sem.2	Final	########	50	26	pass
12	South	111	Cristina Alvarez	2012	Sem.2	T1	########	20	15	pass
13	South	111	Cristina Alvarez	2012	Sem.2	PROJECT	########	100	88	pass
14	South	111	Cristina Alvarez	2012	Sem.3	R1	########	50	39	pass
15	South	111	Cristina Alvarez	2012	Sem.3	T2	########	20	17	pass
16	South	111	Cristina Alvarez	2012	Sem.3	PROJECT	########	100	78	pass
17	South	111	Cristina Alvarez	2012	Sem.3	T1	########	20	12	pass
18	South	111	Cristina Alvarez	2012	Sem.3	T3	########	20	6	fail
19	South	111	Cristina Alvarez	2012	Sem.3	Final	########	50	28	pass
20	South	111	Cristina Alvarez	2012	Sem.3	T4	########	20	11	pass
21	South	111	Cristina Alvarez	2012	Sem.4	R1	########	50	30	pass
22	South	111	Cristina Alvarez	2012	Sem.4	Final	########	50	26	pass
23	South	111	Cristina Alvarez	2012	Sem.4	PROJECT	########	100	86	pass
24	South	111	Cristina Alvarez	2012	Sem.4	T3	########	20	12	pass
25	South	111	Cristina Alvarez	2012	Sem.4	T2	########	20	12	pass
26	South	111	Cristina Alvarez	2012	Sem.4	T1	########	20	8	pass
27	South	111	Cristina Alvarez	2012	Sem.5	T2	########	20	10	pass
28	South	111	Cristina Alvarez	2012	Sem.5	T3	########	20	11	pass
29	South	111	Cristina Alvarez	2012	Sem.5	T1	########	20	9	pass
30	South	111	Cristina Alvarez	2012	Sem.5	Final	########	50	10	fail
31	South	111	Cristina Alvarez	2012	Sem.6	Final	########	50	22	pass
32	South	111	Cristina Alvarez	2012	Sem.6	T1	########	20	10	pass
33	South	111	Cristina Alvarez	2012	Sem.6	PROJECT	########	100	75	pass
34	South	111	Cristina Alvarez	2012	Sem.6	T2	########	20	12	pass
35	South	111	Cristina Alvarez	2012	Sem.6	R1	########	50	25	pass
36	South	888	Jordan Norris	2015	Sem.1	Mod Exam 3	########	20	16	pass
37	South	888	Jordan Norris	2015	Sem.1	Mod Exam 2	########	20	15	pass
38	South	888	Jordan Norris	2015	Sem.1	Mod Exam 1	########	20	10	pass
39	South	888	Jordan Norris	2015	Sem.1	Mod Exam 7	########	20	8	pass
40	South	888	Jordan Norris	2015	Sem.1	Mod Exam 6	########	20	14	pass
41	South	888	Jordan Norris	2015	Sem.1	Mod Exam 5	########	20	10	pass
42	South	888	Jordan Norris	2015	Sem.1	R2	########	20	20	pass
43	South	888	Jordan Norris	2015	Sem.1	R1	########	20	20	pass
44	South	888	Jordan Norris	2015	Sem.1	PROJECT	########	100	78	pass
45	South	888	Jordan Norris	2015	Sem.1	R3	########	20	15	pass
46	South	888	Jordan Norris	2015	Sem.1	Final	########	50	21	pass
47	South	888	Jordan Norris	2015	Sem.1	R5	########	20	10	pass
48	South	888	Jordan Norris	2015	Sem.1	R4	########	20	20	pass
49	South	888	Jordan Norris	2015	Sem.2	Mod Exam 2	########	20	10	pass
50	South	888	Jordan Norris	2015	Sem.2	Mod Exam 1	########	20	15	pass
51	South	888	Jordan Norris	2015	Sem.2	Mod Exam 3	########	20	10	pass
52	South	888	Jordan Norris	2015	Sem.2	Mod Exam 5	########	20	11	pass
53	South	888	Jordan Norris	2015	Sem.2	Mod Exam 4	########	20	10	pass
54	South	888	Jordan Norris	2015	Sem.2	Final	########	50	26	pass
55	South	888	Jordan Norris	2015	Sem.2	R1	########	20	20	pass
56	South	888	Jordan Norris	2015	Sem.2	PROJECT	########	100	75	pass
57	South	888	Jordan Norris	2015	Sem.2	R2	########	20	18	pass
58	South	888	Jordan Norris	2015	Sem.2	R4	########	20	8	pass
59	South	888	Jordan Norris	2015	Sem.2	R3	########	20	8	pass
60	South	888	Jordan Norris	2015	Sem.3	Mod Exam 3	########	20	10	pass
61	South	888	Jordan Norris	2015	Sem.3	Mod Exam 2	########	20	9	pass
62	South	888	Jordan Norris	2015	Sem.3	Mod Exam 4	########	20	11	pass
63	South	888	Jordan Norris	2015	Sem.3	Mod Exam 6	########	20	13	pass
64	South	888	Jordan Norris	2015	Sem.3	Mod Exam 5	########	20	11	pass
65	South	888	Jordan Norris	2015	Sem.3	R1	########	20	15	pass
66	South	888	Jordan Norris	2015	Sem.3	PROJECT	########	100	46	pass
67	South	888	Jordan Norris	2015	Sem.3	R2	########	20	15	pass
68	South	888	Jordan Norris	2015	Sem.3	Mod Exam 1	########	20	18	pass
69	South	888	Jordan Norris	2015	Sem.3	Final	########	50	22	pass
70	South	888	Jordan Norris	2015	Sem.4	Mod Exam 1	########	20	8	pass
71	South	888	Jordan Norris	2015	Sem.4	Final	########	50	24	pass
72	South	888	Jordan Norris	2015	Sem.4	Mod Exam 2	########	20	12	pass
73	South	888	Jordan Norris	2015	Sem.4	Mod Exam 4	########	20	10	pass
74	South	888	Jordan Norris	2015	Sem.4	Mod Exam 3	########	20	13	pass
75	South	888	Jordan Norris	2015	Sem.4	R1	########	20	19	pass
76	South	888	Jordan Norris	2015	Sem.4	PROJECT	########	100	72	pass
77	South	888	Jordan Norris	2015	Sem.4	R2	########	20	19	pass
78	South	888	Jordan Norris	2015	Sem.4	R4	########	20	20	pass
79	South	888	Jordan Norris	2015	Sem.4	R3	########	20	20	pass
80	South	888	Jordan Norris	2015	Sem.5	Mod Exam 2	########	20	9	pass
81	South	888	Jordan Norris	2015	Sem.5	Mod Exam 1	########	20	13	pass
82	South	888	Jordan Norris	2015	Sem.5	Mod Exam 4	########	20	15	pass
83	South	888	Jordan Norris	2015	Sem.5	Mod Exam 3	########	20	12	pass
84	South	888	Jordan Norris	2015	Sem.5	Final	########	50	26	pass
85	South	888	Jordan Norris	2015	Sem.5	R1	########	20	19	pass
86	South	888	Jordan Norris	2015	Sem.5	PROJECT	########	100	60	pass
87	South	888	Jordan Norris	2015	Sem.5	R3	########	20	19	pass
88	South	888	Jordan Norris	2015	Sem.5	R2	########	20	19	pass
89	South	888	Jordan Norris	2015	Sem.6	Mod Exam 1	########	20	10	pass
90	South	888	Jordan Norris	2015	Sem.6	Mod Exam 2	########	20	10	pass
91	South	888	Jordan Norris	2015	Sem.6	Mod Exam 3	########	20	11	pass
92	South	888	Jordan Norris	2015	Sem.6	R1	########	20	20	pass
93	South	888	Jordan Norris	2015	Sem.6	PROJECT	########	100	92	pass
94	South	888	Jordan Norris	2015	Sem.6	Final	########	50	26	pass
Sheet1

xL2BB Mini sheet for Exam Dictionary

Scenario-2.xlsx
	A	B	C
1	Course Code	Semester Name	Exam Name
2	2012	Sem.1	R1
3	2012	Sem.1	T3
4	2012	Sem.1	T2
5	2012	Sem.1	Final
6	2012	Sem.1	PROJECT
7	2012	Sem.1	T1
8	2012	Sem.2	T2
9	2012	Sem.2	T3
10	2012	Sem.2	R1
11	2012	Sem.2	Final
12	2012	Sem.2	T1
13	2012	Sem.2	PROJECT
14	2012	Sem.3	R1
15	2012	Sem.3	T2
16	2012	Sem.3	PROJECT
17	2012	Sem.3	T1
18	2012	Sem.3	T3
19	2012	Sem.3	Final
20	2012	Sem.3	T4
21	2012	Sem.4	R1
22	2012	Sem.4	Final
23	2012	Sem.4	PROJECT
24	2012	Sem.4	T3
25	2012	Sem.4	T2
26	2012	Sem.4	T1
27	2012	Sem.5	T2
28	2012	Sem.5	T3
29	2012	Sem.5	T1
30	2012	Sem.5	Final
31	2012	Sem.6	Final
32	2012	Sem.6	T1
33	2012	Sem.6	PROJECT
34	2012	Sem.6	T2
35	2012	Sem.6	R1
36	2015	Sem.1	Mod Exam 3
37	2015	Sem.1	Mod Exam 2
38	2015	Sem.1	Mod Exam 1
39	2015	Sem.1	Mod Exam 4
40	2015	Sem.1	Mod Exam 7
41	2015	Sem.1	Mod Exam 6
42	2015	Sem.1	Mod Exam 5
43	2015	Sem.1	R2
44	2015	Sem.1	R1
45	2015	Sem.1	PROJECT
46	2015	Sem.1	R3
47	2015	Sem.1	Final
48	2015	Sem.1	R5
49	2015	Sem.1	R4
50	2015	Sem.2	Mod Exam 2
51	2015	Sem.2	Mod Exam 1
52	2015	Sem.2	Mod Exam 3
53	2015	Sem.2	Mod Exam 5
54	2015	Sem.2	Mod Exam 4
55	2015	Sem.2	Final
56	2015	Sem.2	R1
57	2015	Sem.2	PROJECT
58	2015	Sem.2	R2
59	2015	Sem.2	R4
60	2015	Sem.2	R3
61	2015	Sem.3	Mod Exam 3
62	2015	Sem.3	Mod Exam 2
63	2015	Sem.3	Mod Exam 4
64	2015	Sem.3	Mod Exam 6
65	2015	Sem.3	Mod Exam 5
66	2015	Sem.3	R1
67	2015	Sem.3	PROJECT
68	2015	Sem.3	R2
69	2015	Sem.3	Mod Exam 1
70	2015	Sem.3	Final
71	2015	Sem.4	Mod Exam 1
72	2015	Sem.4	Final
73	2015	Sem.4	Mod Exam 2
74	2015	Sem.4	Mod Exam 4
75	2015	Sem.4	Mod Exam 3
76	2015	Sem.4	R1
77	2015	Sem.4	PROJECT
78	2015	Sem.4	R2
79	2015	Sem.4	R4
80	2015	Sem.4	R3
81	2015	Sem.5	Mod Exam 2
82	2015	Sem.5	Mod Exam 1
83	2015	Sem.5	Mod Exam 4
84	2015	Sem.5	Mod Exam 3
85	2015	Sem.5	Final
86	2015	Sem.5	R1
87	2015	Sem.5	PROJECT
88	2015	Sem.5	R3
89	2015	Sem.5	R2
90	2015	Sem.6	Mod Exam 1
91	2015	Sem.6	Mod Exam 2
92	2015	Sem.6	Mod Exam 3
93	2015	Sem.6	R1
94	2015	Sem.6	PROJECT
95	2015	Sem.6	Final
Sheet2

 

[goko]

Hi
Well completed the task manually (and not through Power Query)

1. Separated Exam Records by curriculum to as there are different Exams per semester in different curriculum
2. Used a countifs to identify if particular exams were passed
3. Created a Summary sheet with all relevant students

Sample Sheet

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS
1							ACCP-Semester 1	ACCP-Semester 1	ACCP-Semester 1	ACCP-Semester 1	ACCP-Semester 1	ACCP-Semester 1		ACCP-Semester 2	ACCP-Semester 2	ACCP-Semester 2	ACCP-Semester 2	ACCP-Semester 2	ACCP-Semester 2		ACCP-Semester 3	ACCP-Semester 3	ACCP-Semester 3	ACCP-Semester 3	ACCP-Semester 3	ACCP-Semester 3	ACCP-Semester 3		ACCP-Semester 4	ACCP-Semester 4	ACCP-Semester 4	ACCP-Semester 4	ACCP-Semester 4	ACCP-Semester 4		ACCP-Semester 5	ACCP-Semester 5	ACCP-Semester 5	ACCP-Semester 5		ACCP-Semester 6	ACCP-Semester 6	ACCP-Semester 6	ACCP-Semester 6	ACCP-Semester 6
2	School	Enrollment No	Student Name	Course Code	All Semester Status	Sem.1 Status	e Project	E1	R1	T1	T2	T3	Sem.2 Status	E1	PROJECT	R1	T1	T2	T3	Sem.3 Status	E1	PROJECT	R1	T1	T2	T3	T4	Sem.4 Status	E1	PROJECT	R1	T1	T2	T3	Sem.5 Status	E1	T1	T2	T3	Sem.6 Status	E1	PROJECT	R1	T1	T2
3	South	1111	Daren	2011	TRUE	ok	1	1	1	1	1	1	ok	1	1	1	1	1	1	ok	1	1	1	1	1	1	1	ok	1	1	1	1	1	1	ok	1	1	1	1	ok	1	1	1	1	1
Sheet1