Crispo Excel Challenge 11/25/2023 - Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RemoveLetters = Table.AddColumn(Source, "Custom", each Text.Remove([Visits Tracker], {"A".."z","-"})),
    Trim = Table.TransformColumns(RemoveLetters,{{"Custom", Text.Trim, type text}}),
    SplitSpacetoRows = Table.ExpandListColumn(Table.TransformColumns(Trim, {{"Custom", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    Filter = Table.SelectRows(SplitSpacetoRows, each ([Custom] <> "")),
    SplitSlashtoRows = Table.ExpandListColumn(Table.TransformColumns(Filter, {{"Custom", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
    ReType = Table.TransformColumnTypes(SplitSlashtoRows,{{"Custom", Int64.Type}}),
    Add0Index = Table.AddIndexColumn(ReType, "Index", 0, 1, Int64.Type),
    Modulo2 = Table.AddColumn(Add0Index, "Modulo", each Number.Mod([Index], 2), type number),
    ChgSign = Table.AddColumn(Modulo2, "Value", each if [Modulo] = 0 then -1 * [Custom] else [Custom]),
    Group = Table.Group(ChgSign, {"Visits Tracker"}, {{"Remaining Visits", each List.Max([Remaining Visits]), Int64.Type}, {"Answer", each List.Sum([Value]), Int64.Type}})
in
    Group