Excel BI Power Query Challenge 39 - Brian Julius Solution

gistfile1.txt

let
    Source = Table.DuplicateColumn( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "Month-Year", "Mo-Yr"),
    Split = Table.SplitColumn(Source, "Mo-Yr", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Month", "Year"}),
    ReType = Table.TransformColumnTypes(Split,{{"Month", Int64.Type}, {"Year", Int64.Type}}),
    AddFY = Table.AddColumn(ReType, "FY", each if [Month] < 4 then [Year] else [Year] + 1),
    GroupSort = Table.Group(AddFY, {"Name", "FY"}, List.Sort( {{"All", each _, type table [Name=text, #"Month-Year"=text, Sales=number, Month=nullable number, Year=nullable number, FY=number]}}, each {"Month", Order.Ascending})),
    RTTable = Table.AddColumn(GroupSort, "RunTot", each [
        Idx = Table.AddIndexColumn( [All], "Index", 1, 1),
        SalesAmt = Idx[Sales],
        MoYr = Idx[#"Month-Year"],
        BuffValues = List.Buffer(SalesAmt),
        RunTot = List.Generate ( 
                () => [RT = BuffValues{0}, RowIndex = 0],
                each [RowIndex] < List.Count(BuffValues),
                each [ RT = List.Sum( { [RT] , BuffValues{[RowIndex] + 1} } ),
                       RowIndex = [RowIndex] + 1],
            each [RT]
        )
    ]),
    AddResult = Table.RemoveColumns( Table.AddColumn(RTTable, "Result", each Record.ToTable( Record.RemoveFields( [RunTot], {"Idx", "SalesAmt", "BuffValues"}) )), {"All", "RunTot"}),
    AddZipper = Table.AddColumn(AddResult, "Zipper", each List.Zip( { [Result][Value]{0}, [Result][Value]{1} } )),
    ExpandZipper = Table.ExpandListColumn(AddZipper, "Zipper"),
    ExtractZipper = Table.RemoveColumns( Table.TransformColumns(ExpandZipper, {"Zipper", each Text.Combine(List.Transform(_, Text.From), ","), type text}), {"FY"}),
    ReSplit = Table.SplitColumn(ExtractZipper, "Zipper", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Zipper.1", "Zipper.2"}),
    ReType2 = Table.TransformColumnTypes(ReSplit,{{"Zipper.1", type text}, {"Zipper.2", Int64.Type}}),
    Rename = Table.RenameColumns(ReType2,{{"Zipper.1", "Month-Year"}, {"Zipper.2", "Running Total"}}),
    Join = Table.RemoveColumns( Table.Join( Source, {"Name", "Month-Year"}, Rename, {"Name", "Month-Year"}), {"Mo-Yr", "Result"})
in
    Join