Brian Julius Solution to Excel BI Excel Challenge 420

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Lookup = Table.AddIndexColumn( Table.SelectColumns( Source, {"Color", "Code"} ), "Value", 0, 1),
    T1 = Table.SelectRows( Table.FromColumns( { Source[Color Bands], {1..9}}, {"Bands", "Index"} ), each [Bands] <> null),
    SplitToRows = Table.ExpandListColumn(Table.TransformColumns(T1, {{"Bands", Splitter.SplitTextByRepeatedLengths(2), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Bands"),
    Group = Table.Group(SplitToRows, {"Index"}, {{"MaxCounter", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Bands=nullable text, Index=number]}}),
    AddCount = Table.RemoveColumns( Table.AddColumn(Group, "All2", each Table.AddIndexColumn([All], "Counter", 1, 1)), "All"),
    Expand = Table.ExpandTableColumn(AddCount, "All2", {"Bands", "Counter"}, {"Bands", "Counter"}),
    Join = Table.Join( Expand, "Bands", Lookup, "Code"),
    AddAnswer = Table.AddColumn(Join, "Answer", each if [Counter] = [MaxCounter] then Text.Repeat("0",  [Value] ) else Text.From([Value])),
    SelCols = Table.SelectColumns(AddAnswer,{"Index", "Counter", "Answer"}),
    Sort = Table.Buffer( Table.Sort(SelCols,{{"Index", Order.Ascending}, {"Counter", Order.Ascending}})),
    Regroup = Table.Group(Sort, {"Index"}, {"AnswerExpected", each [Answer], type list}),
    Extract = Table.RemoveColumns( Table.TransformColumns(Regroup, {"AnswerExpected", each Text.Combine(List.Transform(_, Text.From)), type text}), "Index")
in
    Extract