ExcelBI Power Query Challenge 83 – Brian Julius Solution

gistfile1.txt

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  AddQPrefix = Table.TransformColumns(Source, {{"Question No", each "Q" & Text.From(_, "en-US"), type text}}),
  AddCorrect = Table.AddColumn(AddQPrefix, "Correct", each if [Selected Answer] = [Right Answer] then "Y" else "N"),
  AddBincorrect = Table.AddColumn(AddCorrect, "BinCorrect", each if [Selected Answer] = [Right Answer] then 1 else 0),
  Group = Table.Group(AddBincorrect, {"Student"}, {{"QAnswered", each Table.RowCount(_), Int64.Type}, {"QCorrect", each List.Sum([BinCorrect]), type number}, {"All", each _, type table [Student=text, Question No=text, Right Answer=text, Selected Answer=text, Correct=text, BinCorrect=number]}}),
  #"Add%Score" = Table.AddColumn(Group, "%ageScore", each Value.Divide( [QCorrect], [QAnswered] ), Percentage.Type),
  Expand = Table.RemoveColumns( Table.ExpandTableColumn(#"Add%Score", "All", {"Question No", "Correct"}, {"Question No", "Correct"}), {"QAnswered", "QCorrect"}),
  Reorder = Table.ReorderColumns(Expand,{"Student", "%ageScore", "Question No", "Correct"}),
  Pivot = Table.RemoveColumns( Table.Pivot(Reorder, List.Distinct(Reorder[#"Question No"]), "Question No", "Correct"), "%ageScore"),
  Join = Table.Join( Pivot, "Student", #"Add%Score", "Student"),
  Clean = Table.RemoveColumns(Join,{"QAnswered", "QCorrect", "All"})
in
  Clean