Prepare the result table on the basis of problem tables 1 & 2. Populate Y if correction option was chosen otherwise N.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 247
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Prepare the result table on with Power Query
Power Query solution 1 for Prepare the result table on, proposed by Kris Jaganah:
let
A = (x) => Excel.CurrentWorkbook(){[Name = x]}[Content],
B = Table.AddColumn(
A("Table1"),
"Ans",
each if A("Table2")[Correct Option]{[Question] - 1} = [Option Chosen] then "Y" else "N"
),
C = Table.TransformColumns(B, {"Question", each "Q" & Text.From(_)})[[Name], [Question], [Ans]],
D = Table.Pivot(C, List.Distinct(C[Question]), "Question", "Ans"),
E = Table.AddColumn(D, "Score", each List.Count(List.Select(Record.ToList(_), (x) => x = "Y")))
in
EPower Query solution 2 for Prepare the result table on, proposed by Aditya Kumar Darak 🇮🇳:
let
data = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Crct = Excel.CurrentWorkbook(){[Name = "crct"]}[Content],
Join = Table.Join(data, "Question", Crct, "Question"),
Check = Table.CombineColumns(
Join,
{"Option Chosen", "Correct Option"},
each if List.IsDistinct(_) then "N" else "Y",
"Q"
),
Text = Table.TransformColumns(Check, {"Question", each "Q" & Text.From(_)}),
Pivot = Table.Pivot(Text, List.Distinct(Text[Question]), "Question", "Q"),
Return = Table.AddColumn(
Pivot,
"Score",
each List.Count(List.Select(Record.FieldValues(_), (f) => f = "Y"))
)
in
ReturnPower Query solution 3 for Prepare the result table on, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
T1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
T2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
A = Table.RemoveColumns(
Table.AddColumn(
T1,
"A",
(x) =>
let
a = Table.SelectRows(T2, each [Question] = x[Question])[Correct Option]{0},
b = if a = x[Option Chosen] then "Y" else "N"
in
b
),
"Option Chosen"
),
Type = Table.TransformColumns(A, {{"Question", each "Q" & Text.From(_)}}),
Pivot = Table.Pivot(Type, List.Distinct(Type[Question]), "Question", "A"),
Sol = Table.AddColumn(
Pivot,
"Score",
each List.Count(List.Select(List.Skip(Record.ToList(_)), each _ = "Y"))
)
in
SolPower Query solution 4 for Prepare the result table on, proposed by Luan Rodrigues:
let
Fonte = Table.FromRecords(
Table.TransformRows(
Tabela1,
each _
& [
Option Chosen =
if [Option Chosen]
= Table.SelectRows(Tabela2, (x) => [Question] = x[Question])[Correct Option]{0}
then
"Y"
else
"N",
Question = "Q" & Text.From([Question])
]
)
),
pv = Table.Pivot(Fonte, List.Distinct(Fonte[Question]), "Question", "Option Chosen"),
res = Table.AddColumn(
pv,
"Score",
each List.Count(List.Select(List.Skip(Record.FieldValues(_)), (x) => x = "Y"))
)
in
resPower Query solution 5 for Prepare the result table on, proposed by Abdallah Ally:
let
Table = each Excel.CurrentWorkbook(){[Name = _]}[Content],
Join = Table.Join(Table("Table1"), "Question", Table("Table2"), "Question"),
Transform = Table.TransformColumns(Join, {"Question", each "Q" & Text.From(_)}),
AddCol = Table.AddColumn(
Transform,
"Correct",
each if [Option Chosen] = [Correct Option] then "Y" else "N"
),
Pivot = Table.Pivot(
AddCol[[Name], [Question], [Correct]],
List.Distinct(AddCol[Question]),
"Question",
"Correct"
),
Result = Table.AddColumn(
Pivot,
"Score",
each List.Count(List.Select(Record.ToList(_), (x) => x = "Y"))
)
in
ResultPower Query solution 6 for Prepare the result table on, proposed by Abdallah Ally:
let
Table = each Excel.CurrentWorkbook(){[Name = _]}[Content],
Join = Table.Join(Table("Table1"), "Question", Table("Table2"), "Question"),
Transform = Table.TransformColumns(Join, {"Question", each "Q" & Text.From(_)}),
AddCol = Table.AddColumn(
Transform,
"Correct",
each if [Option Chosen] = [Correct Option] then "Y" else "N"
),
Select = Table.SelectColumns(AddCol, {"Name", "Question", "Correct"}),
Pivot = Table.Pivot(Select, List.Distinct(Select[Question]), "Question", "Correct"),
Result = Table.AddColumn(
Pivot,
"Score",
each List.Count(List.Select(Record.ToList(_), (x) => x = "Y"))
)
in
ResultPower Query solution 7 for Prepare the result table on, proposed by Eric Laforce:
let
fxSource = (n) => Excel.CurrentWorkbook(){[Name = n]}[Content],
Join = Table.Join(
fxSource("tData247_1"),
"Question",
fxSource("tData247_2"),
"Question",
JoinKind.Inner
),
CR = Table.CombineColumns(
Join,
{"Option Chosen", "Correct Option"},
(x) => if (x{0} = x{1}) then "Y" else "N",
"R"
),
TQ = Table.TransformColumns(CR, {"Question", each "Q" & Text.From(_)}),
Pivot = Table.Pivot(TQ, List.Distinct(TQ[Question]), "Question", "R", each _{0}),
AddScore = Table.AddColumn(
Pivot,
"Score",
each List.Count(List.Select(Record.FieldValues(_), each _ = "Y"))
)
in
AddScorePower Query solution 8 for Prepare the result table on, proposed by Seokho MOON:
let
tbl_1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
tbl_2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Merge = Table.NestedJoin(tbl_1, {"Question"}, tbl_2, {"Question"}, "tbl_1", JoinKind.LeftOuter),
Expand = Table.TransformColumns(
Table.ExpandTableColumn(Merge, "tbl_1", {"Correct Option"}),
{"Question", each "Q" & Text.From(_)}
),
M = Table.AddColumn(Expand, "M", each if [Option Chosen] = [Correct Option] then "Y" else "N")[
[Name],
[Question],
[M]
],
Pivot = Table.Pivot(M, List.Distinct(M[Question]), "Question", "M"),
Score = Table.AddColumn(
Pivot,
"Score",
each List.Count(List.Select(List.Skip(Record.ToList(_)), each _ = "Y"))
)
in
ScorePower Query solution 9 for Prepare the result table on, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
S1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(
S1,
"Answer",
each if [Option Chosen] = S2[#"Correct Option"]{[Question] - 1} then "Y" else "N"
),
B = Table.TransformColumns(A, {{"Question", each "Q" & Text.From(_, "en-US"), type text}}),
C = Table.SelectColumns(B, {"Name", "Question", "Answer"}),
D = Table.Pivot(C, List.Distinct(C[Question]), "Question", "Answer"),
E = Table.AddColumn(
D,
"Score",
each List.Count(List.RemoveItems(List.Skip(Record.ToList(_), 1), {"N"}))
)
in
EPower Query solution 10 for Prepare the result table on, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Q_1"]}[Content],
Merge = Table.NestedJoin(Source, {"Question"}, Q_2, {"Question"}, "Q_2", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Merge, "Q_2", {"Correct Option"}, {"Correct Option"}),
#"Y/N" = Table.AddColumn(
Expand,
"Y/N",
each if [Option Chosen] = [Correct Option] then "Y" else "N"
),
Q = Table.TransformColumns(#"Y/N", {{"Question", each "Q " & Text.From(_, "en-IN"), type text}}),
Remove = Table.SelectColumns(Q, {"Name", "Question", "Y/N"}),
Pivot = Table.Pivot(Remove, List.Distinct(Remove[Question]), "Question", "Y/N"),
Score = Table.AddColumn(
Pivot,
"Score",
each List.Count(List.Select(List.Skip(Record.ToList(_)), each _ = "Y"))
)
in
ScorePower Query solution 11 for Prepare the result table on, proposed by Yaroslav Drohomyretskyi:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
Mark = Table.AddColumn(
Source("Table1"),
"Mark",
each
if Table.SelectRows(Source("Table2"), (x) => x[Question] = [Question])[Correct Option]{0}
= [Option Chosen]
then
"Y"
else
"N"
),
Q = Table.ReplaceValue(
Mark,
each [Question],
each "Q" & Text.From([Question]),
Replacer.ReplaceValue,
{"Question"}
)[[Name], [Question], [Mark]],
Pivot = Table.Pivot(Q, List.Distinct(Q[Question]), "Question", "Mark"),
Score = Table.AddColumn(
Pivot,
"Score",
each List.Count(List.Select(Record.FieldValues(_), each _ = "Y"))
)
in
ScorePower Query solution 12 for Prepare the result table on, proposed by Alexandre Garcia:
let
A = each Excel.CurrentWorkbook(){[Name = _]}[Content],
B = A("Table2"),
C = Table.ToColumns,
D = List.Transform,
E = Table.Combine(
Table.Group(
A("Table1"),
"Name",
{
"x",
each [
a = List.Zip({C(_){2}, C(B){1}}),
b = D(a, (x) => Byte.From(x{0} = x{1})),
c = D(b, (x) => if x = 0 then "N" else "Y"),
d = Table.FromRows(
{{[Name]{0}} & c & {List.Sum(b)}},
{"Name"} & D(C(B){0}, each "Q" & Text.From(_)) & {"Score"}
)
][d]
}
)[x]
)
in
ESolving the challenge of Prepare the result table on with Excel
Excel solution 1 for Prepare the result table on, proposed by Bo Rydobon 🇹🇭:
=LET(p,
PIVOTBY(
A2:A13,
"Q"&B2:B13,
N(
C2:C13=LOOKUP(
B2:B13,
A17:B20
)
),
SUM,
,
0
),
SWITCH(p,
"",
"Name",
"Total",
"Score",
IF((LEFT(
TAKE(
p,
1
)
)="Q")*(TAKE(
p,
,
1
)>""),
IF(
p,
"Y",
"N"
),
p)))Excel solution 2 for Prepare the result table on, proposed by Rick Rothstein:
=LET(b,
B17:B20,
r,
ROWS(
b
),
u,
UNIQUE(
A2:A13
),
n,
C2:C13=INDEX(
b,
1+MOD(
SEQUENCE(
4*ROWS(
u
)
)-1,
r
)
),
y,
WRAPROWS(
IF(
n,
"Y",
"N"
),
r
),
HSTACK(u,
y,
BYROW(0+(y="Y"),
SUM)))Excel solution 3 for Prepare the result table on, proposed by 🇰🇷 Taeyong Shin:
=LET(
b,
WRAPROWS(
C2:C13,
4
)=TOROW(
B17:B20
),
VSTACK(
TOROW(
VSTACK(
A1,
"Q"&UNIQUE(
B2:B13
),
"Score"
)
),
HSTACK(
UNIQUE(
A2:A13
),
IF(
b,
"Y",
"N"
),
MMULT(
--b,
SIGN(
A17:A20
)
)
)
)
)Excel solution 4 for Prepare the result table on, proposed by Kris Jaganah:
=LET(
a,
B2:B13,
b,
IF(
VLOOKUP(
a,
A17:B20,
2,
0
)=C2:C13,
"Y",
"N"
),
c,
PIVOTBY(
A2:A13,
"Q"&a,
b,
CONCAT,
,
0,
,
0
),
d,
HSTACK(
c,
VSTACK(
"Score",
BYROW(
N(
DROP(
c,
1
)="Y"
),
SUM
)
)
),
IF(
d="",
"Name",
d
)
)Excel solution 5 for Prepare the result table on, proposed by Julian Poeltl:
=LET(
Q,
B2:B13,
U,
UNIQUE(
A2:A13
),
W,
WRAPROWS(
TOROW(
MAP(
Q,
LAMBDA(
A,
XLOOKUP(
A,
A17:A20,
B17:B20
)
)
)=C2:C13
),
4
),
VSTACK(
HSTACK(
"Name",
"Q"&TOROW(
UNIQUE(
Q
)
),
"Score"
),
HSTACK(
U,
IF(
W,
"Y",
"N"
),
BYROW(
W,
LAMBDA(
A,
SUM(
--A
)
)
)
)
)
)Excel solution 6 for Prepare the result table on, proposed by Aditya Kumar Darak 🇮🇳:
=LET(_pivot,
PIVOTBY(
A2:A13,
"Q" & B2:B13,
IF(
XLOOKUP(
B2:B13,
A17:A20,
B17:B20
) = C2:C13,
"Y",
"N"
),
SINGLE,
0,
0,
,
0
),
_return,
HSTACK(_pivot,
VSTACK("Score",
BYROW(--(DROP(
_pivot,
1,
1
) = "Y"),
SUM))),
_return)Excel solution 7 for Prepare the result table on, proposed by Hussein SATOUR:
=LET(
q,
B2:B13,
a,
PIVOTBY(
A2:A13,
"Q"&q,
IF(
XLOOKUP(
q,
A17:A20,
B17:B20
)=C2:C13,
"Y",
"N"
),
SINGLE,
,
0,
,
0
),
HSTACK(
a,
VSTACK(
"Score",
BYROW(
DROP(
a,
1,
1
),
LAMBDA(
x,
LEN(
SUBSTITUTE(
CONCAT(
x
),
"N",
""
)
)
)
)
)
)
)Excel solution 8 for Prepare the result table on, proposed by Oscar Mendez Roca Farell:
=LET(
p,
PIVOTBY(
A2:A13,
"Q"&B2:B13,
B2:B13&C2:C13,
LAMBDA(
& i,
COUNT(
FIND(
i,
CONCAT(
A17:B20
)
)
)
),
,
0
),
IFERROR(
IF(
HSTACK(
DROP(
p,
,
-1
)>"",
TAKE(
p,
,
-1
)>0
),
p,
IF(
p,
"Y",
"N"
)
),
"Name"
)
)Excel solution 9 for Prepare the result table on, proposed by Oscar Mendez Roca Farell:
=LET(
p,
PIVOTBY(
A2:A13,
"Q"&B2:B13,
B2:B13&C2:C13,
LAMBDA(
i,
COUNT(
FIND(
i,
CONCAT(
A17:B20
)
)
)
),
,
0,
,
0
),
b,
BYROW(
p,
SUM
),
HSTACK(
IFERROR(
IF(
-p,
"Y",
"N"
),
p
),
IF(
b,
b,
"Score"
)
)
)Excel solution 10 for Prepare the result table on, proposed by Duy Tùng:
=LET(
a,
A2:A13,
b,
PIVOTBY(
a,
"Q"&MAP(
a,
LAMBDA(
x,
SUM(
N(
A2:x=x
)
)
)
),
IF(
N(
LOOKUP(
B2:B13,
A17:B20
)=C2:C13
),
"Y",
"N"
),
SINGLE,
,
0,
,
0
),
VSTACK(
HSTACK(
A1,
DROP(
TAKE(
b,
1
),
,
1
),
"Score"
),
HSTACK(
DROP(
b,
1
),
BYROW(
N(
DROP(
b,
1
)="Y"
),
SUM
)
)
)
)Excel solution 11 for Prepare the result table on, proposed by Sunny Baggu:
=LET(
_n,
UNIQUE(
A2:A13
),
_q,
TOROW(
UNIQUE(
B2:B13
)
),
_c,
XLOOKUP(
_n & _q,
A2:A13 & B2:B13,
C2:C13
) =
TOROW(
B17:B20
),
_r,
IF(
_c,
"Y",
"N"
),
_s,
BYROW(
_c + 0,
LAMBDA(
a,
SUM(
a
)
)
),
VSTACK(
HSTACK(
A1,
"Q" & _q,
"Score"
),
HSTACK(
_n,
_r,
_s
)
)
)Excel solution 12 for Prepare the result table on, proposed by Md. Zohurul Islam:
=LET(
a,
A2:A13,
b,
B2:B13,
c,
C2:C13,
u,
A17:A20,
v,
B17:B21,
nam,
UNIQUE(
a
),
d,
DROP(
TEXTSPLIT(
REPT(
ARRAYTOTEXT(
v
),
COUNTA(
nam
)
),
,
", "
),
-1
),
e,
MAP(
c,
d,
LAMBDA(
x,
y,
IF(
x=y,
"Y",
"N"
)
)
),
f,
REDUCE(
"Q"&TOROW(
u
),
nam,
LAMBDA(
x,
y,
VSTACK(
x,
TOROW(
FILTER(
e,
a=y
)
)
)
)
),
g,
VSTACK(
"Score",
BYROW(
DROP(
f,
1
),
LAMBDA(
x,
SUM(
ABS(
x="Y"
)
)
)
)
),
h,
HSTACK(
VSTACK(
A1,
nam
),
f,
g
),
h
)Excel solution 13 for Prepare the result table on, proposed by Pieter de B.:
=LET(
x,
C2:C13=LOOKUP(
B2:B13,
A17:B20
),
y,
HSTACK(
PIVOTBY(
A2:A13,
"Q"&B2:B13,
IF(
x,
"Y",
"N"
),
SINGLE,
,
0,
,
0
),
VSTACK(
"Score",
BYROW(
--WRAPROWS(
x,
4
),
SUM
)
)
),
IF(
y="",
"Name",
y
)
)Excel solution 14 for Prepare the result table on, proposed by Hamidi Hamid:
=LET(x,
UNIQUE(
A2:A13
),
y,
TOROW(
UNIQUE(
B2:B13
)
),
z,
XLOOKUP(
x&y,
A2:A13&B2:B13,
C2:C13,
""
),
t,
TRANSPOSE(
IF(
SEQUENCE(
,
3
),
B17:B20,
""
)
),
u,
IF((t=z)*1=0,
"N",
"Y"),
VSTACK(E1:J1,
VSTACK(HSTACK(UNIQUE(
A2:A13
),
u,
BYROW((t=z)*1,
SUM)))))Excel solution 15 for Prepare the result table on, proposed by Asheesh Pahwa:
=LET(
n,
A2:A13,
u,
UNIQUE(
n
),
r,
REDUCE(
"",
u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
B2:C13,
n=y
),
t,
TAKE(
f,
,
1
),
xl,
XLOOKUP(
t,
A17:A20,
B17:B20
),
_t,
TOROW(
N(
xl=INDEX(
f,
,
2
)
)
),
s,
SUM(
_t
),
HSTACK(
y,
IF(
_t,
"Y",
"N"
),
s
)
)
)
)
),
VSTACK(
E1:J1,
DROP(
r,
1
)
)
)Excel solution 16 for Prepare the result table on, proposed by ferhat CK:
=LET(
a,
PIVOTBY(
A2:A13,
B2:B13,
IF(
ISNUMBER(
XMATCH(
B2:B13&C2:C13,
A17:A20&B17:B20
)
),
"Y",
"N"
),
ARRAYTOTEXT,
,
0,
,
0,
,
),
c,
IF(
ISNUMBER(
a
),
"Q"&a,
a
),
b,
HSTACK(
c,
VSTACK(
"Score",
DROP(
BYROW(
N(
c="Y"
),
SUM
),
1
)
)
),
IFS(
b="",
"Name",
INDEX(
b,
1,
1
)=0,
"Name",
1=1,
b
)
)Excel solution 17 for Prepare the result table on, proposed by Jaroslaw Kujawa:
=LET(
x;
DROP(
TEXTSPLIT(
CONCAT(
BYROW(
B2:B13;
LAMBDA(
x;
LET(
tab;
A17:B20;
TEXTJOIN(
";";
;
OFFSET(
x;
;
-1
);
"Q"&x;
IF(
VLOOKUP(
x;
tab;
2;
)=OFFSET(
x;
;
1
);
"Y";
"N"
);
"|"
)
)
)
)
);
";";
"|"
);
-1;
-1
);
piv;
PIVOTBY(
TAKE(
x;
;
1
);
CHOOSECOLS(
x;
2
);
TAKE(
x;
;
-1
);
CONCAT;
;
0
);
HSTACK(
IF(
TAKE(
piv;
;
1
)<>"";
TAKE(
piv;
;
1
);
"Name"
);
CHOOSECOLS(
piv;
{2;
3;
4;
5}
);
IF(
TAKE(
piv;
;
-1
)<>"Total";
LEN(
TAKE(
piv;
;
-1
)
)-LEN(
SUBSTITUTE(
TAKE(
piv;
;
-1
);
"Y";
""
)
);
"Score"
)
)
)Excel solution 18 for Prepare the result table on, proposed by Jaroslaw Kujawa:
=LET(
a;
IFNA(
IF(
MATCH(
B2:B13&C2:C13;
$A$17:$A$20&$B$17:$B$20;
0
);
1;
0
);
);
yn;
WRAPROWS(
TOROW(
a
);
4
);
VSTACK(
HSTACK(
"Name";
"Q"&SEQUENCE(
;
MAX(
B2:B13
)
);
"Score"
);
HSTACK(
UNIQUE(
A2:A13
);
IF(
yn;
"Y";
"N"
);
BYROW(
yn;
LAMBDA(
x;
SUM(
x
)
)
)
)
)
)Excel solution 19 for Prepare the result table on, proposed by Imam Hambali:
=LET(
i,
IF(
XLOOKUP(
B2:B13,
A17:A20,
B17:B20
)=C2:C13,
"Y",
"N"
),
p,
PIVOTBY(
A2:A13,
"Q"&B2:B13,
i,
ARRAYTOTEXT,
0,
0,
,
0
),
VSTACK(
HSTACK(
"Name",
DROP(
TAKE(
p,
1
),
,
1
),
"Score"
),
HSTACK(
DROP(
p,
1
),
BYROW(
IF(
DROP(
p,
1,
1
)="Y",
1,
0
),
SUM
)
)
)
)Excel solution 20 for Prepare the result table on, proposed by Md Ismail Hosen:
=LAMBDA(StudentsAnswerData,
CorrectAnsMap,
LET(_Names,
CHOOSECOLS(
StudentsAnswerData,
1
),
_Questions,
CHOOSECOLS(
StudentsAnswerData,
2
),
_Answers,
CHOOSECOLS(
StudentsAnswerData,
3
),
_IsCorrectAns,
IF(
VLOOKUP(
_Questions,
CorrectAnsMap,
2,
FALSE
)=_Answers,
"Y",
"N"
),
_PivotedData,
PIVOTBY(
_Names,
"Q"&_Questions,
_IsCorrectAns,
LAMBDA(
a,
ARRAYTOTEXT(
a
)
),
0,
0,
,
0
),
_ScoreCount,
VSTACK("Score",
BYROW(DROP(
_PivotedData,
1,
1
),
LAMBDA(row,
SUM(--(row="Y"))))),
_Result,
HSTACK(
VSTACK(
"Name",
DROP(
CHOOSECOLS(
_PivotedData,
1
),
1
)
),
DROP(
_PivotedData,
,
1
),
_ScoreCount
),
_Result))(A2:C13,
A17:B20)Excel solution 21 for Prepare the result table on, proposed by Stefan Alexandrov:
=LET(
_data,
A2:C13,
_rightans,
IF(
CHOOSECOLS(
_data,
3
)=XLOOKUP(
CHOOSECOLS(
_data,
2
),
$A$17:$A$20,
$B$17:$B$20,
,
0
),
"Y",
"N"
),
_CodedData,
CHOOSECOLS(
HSTACK(
_data,
_rightans
),
1,
2,
4
),
_pivot,
PIVOTBY(
CHOOSECOLS(
_CodedData,
1
),
CHOOSECOLS(
_CodedData,
2
),
CHOOSECOLS(
_CodedData,
3
),
CONCAT,
,
0,
,
0
),
_Logical,
IF(
CHOOSECOLS(
_CodedData,
3
)="Y",
1,
0
),
_counts,
GROUPBY(
CHOOSECOLS(
HSTACK(
_CodedData,
_Logical
),
1
),
CHOOSECOLS(
HSTACK(
_CodedData,
_Logical
),
4
),
SUM,
,
0
),
_table,
HSTACK(
DROP(
_pivot,
1
),
CHOOSECOLS(
_counts,
2
)
),
_header,
{"Name",
"Q1",
"Q2",
"Q3",
"Q4",
"Score"},
VSTACK(
_header,
_table
)
)Excel solution 22 for Prepare the result table on, proposed by abdelaziz kamal allam:
=LET(
x,
TRANSPOSE(
A17:A20
),
xx,
UNIQUE(
A2:A13
),
v,
VSTACK(
{"Name",
1,
2,
3,
4},
HSTACK(
xx,
IF(
XLOOKUP(
xx&x,
$A$2:$A$13&$B$2:$B$13,
& $C$2:$C$13
)=XLOOKUP(
x,
A$17:A$20,
$B$17:$B$20
)=TRUE,
"Y",
"N"
)
)
),
c,
DROP(
v,
1,
1
),
b,
DROP(
v,
1,
1
),
HSTACK(
VSTACK(
{"Name"},
UNIQUE(
A2:A13
)
),
VSTACK(
{"Q1",
"Q2",
"Q3",
"Q4",
"Score"},
HSTACK(
b,
BYROW(
SWITCH(
b,
"Y",
1,
"N",
0
),
LAMBDA(
a,
SUM(
a
)
)
)
)
)
)
)Excel solution 23 for Prepare the result table on, proposed by abdelaziz kamal allam:
=LET(
x,
TRANSPOSE(
A17:A20
),
xx,
UNIQUE(
A2:A13
),
v,
VSTACK(
{"Name",
1,
2,
3,
4},
HSTACK(
xx,
IF(
XLOOKUP(
xx&x,
$A$2:$A$13&$B$2:$B$13,
$C$2:$C$13
)=XLOOKUP(
x,
A$17:A$20,
$B$17:$B$20
)=TRUE,
"Y",
"N"
)
)
),
c,
DROP(
v,
1,
1
),
VSTACK(
"Score",
BYROW(
DROP(
v,
1,
1
),
LAMBDA(
a,
COUNTIF(
a,
"Y"
)
)
)
)
)Solving the challenge of Prepare the result table on with Python
Python solution 1 for Prepare the result table on, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "PQ_Challenge_247.xlsx"
input1 = pd.read_excel(path, usecols="A:C", nrows=13)
input2 = pd.read_excel(path, usecols="A:B", skiprows=15, nrows=5)
test = pd.read_excel(path, usecols="E:J", nrows=3).rename(columns=lambda x: x.split('.')[0])
input = input1.merge(input2, on="Question", how="left")
input['correctness'] = (input['Option Chosen'] == input['Correct Option']).map({True: 'Y', False: 'N'})
input = input.drop(columns=['Option Chosen', 'Correct Option'])
input = input.pivot(index='Name', columns='Question', values='correctness').rename(columns=lambda x: f"Q{x}")
input['Score'] = (input == 'Y').sum(axis=1)
input.reset_index(inplace=True)
print(input.equals(test)) # True
Python solution 2 for Prepare the result table on, proposed by Luan Rodrigues:
import pandas as pd
import numpy as np
file = "PQ_Challenge_247.xlsx"
df1 = pd.read_excel(file,usecols="A:C",nrows=13)
df2 = pd.read_excel(file,usecols="A:B",skiprows=15, nrows=5)
dic = df2.set_index(['Question'])['Correct Option'].to_dict()
df1['Valid'] = np.where(df1['Question'].map(dic) == df1['Option Chosen'], 'Y', 'N')
df1['Question'] = "Q" + df1['Question'].astype('str')
grp = df1.pivot_table(index='Name', columns='Question', values='Valid', aggfunc='first').reset_index()
score = df1[df1['Valid'] == 'Y'].groupby(['Name']).size().reset_index(name='Score')
res = pd.concat([grp,score],axis=1)
print(res)
Python solution 3 for Prepare the result table on, proposed by Abdallah Ally:
import pandas as pd
file_path = 'PQ_Challenge_247.xlsx'
df1 = pd.read_excel(file_path, usecols='A:C', nrows=12)
df2 = pd.read_excel(file_path, usecols='A:B', skiprows=15)
# Perform data manipulation
df = df1.merge(df2, how='inner', on='Question')
df['Correct'] = df.apply(
lambda x: 'Y' if x.loc['Correct Option'] == x.loc['Option Chosen'] else 'N', axis=1
)
df['Question'] = 'Q' + df['Question'].map(str)
df = (
df
.pivot(index='Name', columns='Question', values='Correct')
.rename_axis('', axis=1)
.reset_index()
)
df['Score'] = df.apply(lambda x: list(x).count('Y'), axis=1)
df
Solving the challenge of Prepare the result table on with Python in Excel
Python in Excel solution 1 for Prepare the result table on, proposed by Alejandro Campos:
pivot_table = (
pd.merge(xl("A1:C13", headers=True), xl("A16:B20", headers=True), on='Question')
.assign(Result=lambda df: df.apply(lambda row: "Y" if
row['Option Chosen'] == row['Correct Option'] else "N", axis=1))
.pivot(index='Name', columns='Question', values='Result')
.rename_axis(None, axis=1)
.rename(columns={1: 'Q1', 2: 'Q2', 3: 'Q3', 4: 'Q4'})
.reset_index()
)
pivot_table['Score'] = pivot_table[['Q1', 'Q2', 'Q3', 'Q4']].eq("Y").sum(axis=1)
pivot_table
Solving the challenge of Prepare the result table on with R
R solution 1 for Prepare the result table on, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_247.xlsx"
input1 = read_excel(path, range = "A1:C13")
input2 = read_excel(path, range = "A16:B20")
test = read_excel(path, range = "E1:J4")
input = input1 %>%
left_join(input2, by = "Question") %>%
mutate(correctness = ifelse(`Option Chosen` == `Correct Option`, "Y", "N")) %>%
select(-c(3:4)) %>%
pivot_wider(names_from = Question, values_from = correctness, names_prefix = "Q") %>%
mutate(Score = rowSums(select(., starts_with("Q")) == "Y"))
all.equal(input, test)
#> [1] TRUE
&