Excel BI Challenge 135 – Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Dupe = Table.DuplicateColumn(Source, "String", "String2"),
    #"SplitBy-" = Table.ExpandListColumn(Table.TransformColumns(Dupe, {{"String2", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "String2"),
    SplitByCharTrans = Table.SplitColumn(#"SplitBy-", "String2", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c))),
    Add1stPosCheck = Table.AddColumn(SplitByCharTrans, "FirstPos", each if Text.StartsWith( [String], [String2.1]) then 0 else 1),
    Filter = Table.SelectRows(Add1stPosCheck, each ([FirstPos] = 1)),
    ReType = Table.TransformColumnTypes(Filter,{{"String2.1", Int64.Type}}),
    #"Multiply-1" = Table.AddColumn(ReType, "Multiplication", each [String2.1] * -1, type number),
    GroupSum = Table.Group(#"Multiply-1", {"String"}, {{"Answer", each List.Sum([Multiplication]), type number}}),
    Rename = Table.RenameColumns(GroupSum,{{"String", "String1"}}),
    Join = Table.Join( Source, "String", Rename, "String1", JoinKind.LeftOuter),
    ReplaceNulls = Table.RemoveColumns( Table.ReplaceValue(Join,null,0,Replacer.ReplaceValue,{"Answer"}), "String1")
in
    ReplaceNulls