Excel BI Excel Challenge 248 Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    
    LookupTable = Table.AddColumn( Source, "LookupTbl", each
         [
            DateList = Table.FromList( List.Transform( { Number.From( #date(2023, 1, 1)) .. Number.From( #date(2023, 12, 31))}, each Date.From(_)), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            MonQ = Table.AddColumn(DateList, "MonthQ", each Date.ToText([Column1], "MMM") & "-Q" & Text.From( Date.QuarterOfYear([Column1])), type text),
            Base = Table.Distinct( Table.SelectColumns(MonQ, "MonthQ")),
            AddIndex = Table.AddIndexColumn(Base, "Index", 0, 1, type number),
            AddConstant = Table.AddColumn(AddIndex, "Constant", each 1, Int64.Type)
            ][AddConstant]),
    AddLHS =  Table.AddColumn(LookupTable, "LHS", each [
        a = Text.ToList( [String] ),
        b =Table.FromList( List.PositionOf( a, "1", Occurrence.All), Splitter.SplitByNothing(), null, null, ExtraValues.Error)
        ][b]),
    Join = Table.SelectColumns( Table.AddColumn(AddLHS, "Answer", each try Table.SelectColumns( Table.Join( [LHS], "Column1", [LookupTbl], "Index"), "MonthQ") otherwise null ), {"String","Answer"}),
    Group = Table.Group( Table.ExpandTableColumn(Join, "Answer", {"MonthQ"}, {"MonthQ"}), {"String"}, {{"All", each [MonthQ]}}),
    Extract = Table.TransformColumns(Group, {"All", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    Extract