Excel BI Power Query Challenge 105 - Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddIdx = Table.Buffer(Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type)),
    SplitbyCharTrans = Table.SplitColumn(AddIdx, "Text Time", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Text Time.1", "Text Time.2", "Text Time.3", "Text Time.4"}),
    UnpivotOther = Table.RemoveColumns(Table.UnpivotOtherColumns(SplitbyCharTrans, {"Add", "Index"}, "Attribute", "Value"), {"Attribute", "Add"}),
    SplitbySpace = Table.TransformColumnTypes( Table.SplitColumn(UnpivotOther, "Value", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Value.1", "Value.2"}), {"Value.1", Number.Type}),
    FirstChar = Table.RemoveColumns( Table.AddColumn(SplitbySpace, "Category", each Text.Start(Text.Upper([Value.2]), 1), type text), {"Value.2"}),
    ConvertSecs = Table.AddColumn(FirstChar, "Seconds", each if [Category] = "S" then [Value.1] else
        if [Category] = "M" then [Value.1] * 60 else 
        if [Category] = "H" then [Value.1] * 3600 else
        if [Category] = "D" then [Value.1] * 86400 else 
        -9999),
    ValueTable = Table.RemoveColumns( AddIdx, {"Text Time"}),
    Split = Table.SplitColumn(ValueTable, "Add", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Add.1", "Add.2"}),
    FirstChar2 = Table.TransformColumnTypes( Table.AddColumn(Split, "Category2", each Text.Start(Text.Upper([Add.2]), 1), type text), {"Add.1", Number.Type}),
    ConvertSecs2 = Table.AddColumn(FirstChar2, "ConvertSecs2", each if [Category2] = "S" then [Add.1] else
        if [Category2] = "M" then [Add.1] * 60 else 
        if [Category2] = "H" then [Add.1] * 3600 else
        if [Category2] = "D" then [Add.1] * 86400 else 
        -9999),
    SelectCols = Table.SelectColumns(ConvertSecs2,{"Index", "ConvertSecs2"}),
    Join = Table.Join(ConvertSecs, "Index", SelectCols, "Index"),
    GroupSumSecs = Table.Group(Join, {"Index"}, {{"TotalSecs", each List.Sum([Seconds]), type number}, {"AddSecs", each List.Max([ConvertSecs2]), type number}}),
    AddDuration = Table.AddColumn(GroupSumSecs, "Duration", each [
        a = [TotalSecs] + [AddSecs],
        b = #duration(0,0,0,a)
    ][b], Duration.Type),
    Join2 = Table.RemoveColumns( Table.Join(AddIdx, "Index", AddDuration, "Index"), {"Index", "TotalSecs", "AddSecs"})
in
    Join2