Excel BI Power Query Challenge 59 - Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddQNumber = Table.FillUp( Table.AddColumn(Source, "QNum", each if [Seq] = "#" then null else [Seq]), {"QNum"}),
    AddQHeader = Table.FillDown( Table.AddColumn(AddQNumber, "QHeader", each if [Seq] = "#" then Text.From( [QNum] ) & ". " & [Question] else null), {"QHeader"}),
    AddIndex = Table.AddIndexColumn(AddQHeader, "Index", 1, 1, Int64.Type),
    Filter = Table.RemoveColumns( Table.SelectRows(AddIndex, each ([Seq] <> "#")), {"Seq", "QNum"}),
    Group = Table.Group(Filter, {"QHeader"}, {{"All", each _, type table [Question=text, Correct=nullable text, QHeader=text, Index=number]}}),
    AddOptionNum = Table.RemoveColumns( Table.AddColumn(Group, "Option", each Table.AddIndexColumn( [All], "Option", 1, 1 )), "All"),
    Expand = Table.ExpandTableColumn(AddOptionNum, "Option", {"Question", "Correct", "Index", "Option"}, {"Question", "Correct", "Index", "Option"}),
    AddOptions = Table.RemoveColumns( Table.AddColumn(Expand, "Options", each "Option " & Text.From( [Option] )), "Option"),
    AnswerTable = Table.RemoveColumns( Table.SelectRows( AddOptions, each [Correct] = "Y"), {"Correct", "Index"}),
    OptionsTable = Table.RemoveColumns( AddOptions, {"Correct", "Index"}),
    PivotOptionsTable = Table.Pivot(OptionsTable, List.Distinct(OptionsTable[QHeader]), "QHeader", "Question"),
    UnpivotBlankTable = Table.AddColumn( Table.RemoveColumns( Table.UnpivotOtherColumns(PivotOptionsTable, {"Options"}, "Attribute", "Value"), "Value"), "Value", each null),
    PivotBlankTable = Table.Pivot(UnpivotBlankTable, List.Distinct(UnpivotBlankTable[Attribute]), "Attribute", "Value"),
    EmptyBlankTable = Table.SelectRows( Table.ReplaceValue(PivotBlankTable,"Option 1",null,Replacer.ReplaceValue,{"Options"}), each [Options] = null),
    PivotAnswerTable = Table.FillUp( Table.Pivot(AnswerTable, List.Distinct(OptionsTable[QHeader]), "QHeader", "Question"), Table.ColumnNames( PivotBlankTable )),
    AnswerRow = Table.SelectRows( Table.ReplaceValue(PivotAnswerTable,"Option 1","Correct Answer",Replacer.ReplaceText,{"Options"}), each [Options] = "Correct Answer"),
    Final = Table.Combine( {PivotOptionsTable, EmptyBlankTable, AnswerRow})

in
    Final