Excel BI Power Query Challenge 41 – Brian Julia Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    ReplaceSeq = Table.RenameColumns( Table.ReplaceValue(Source,"Seq",null,Replacer.ReplaceValue,{"City"}), {"City", "CityTemp"}),
    AddCity = Table.AddColumn(ReplaceSeq, "City", each if [Seq] = null then null else [CityTemp] ),
    ReType = Table.TransformColumnTypes(AddCity,{{"CityTemp", Int64.Type}, {"Column1", type text}}),
    Rename = Table.RenameColumns(ReType,{{"Column1", "DateTemp"}, {"Sub Total", "AmountTemp"}, {"State", "Class"}, {"CityTemp", "No"}}),
    AddState = Table.AddColumn(Rename, "State", each if List.Contains( {"Class", "Local", "Council"}, [Class] ) then null else [Class]),
    FillDown = Table.FillDown(AddState,{"City", "State", "Seq"}),
    Filter = Table.SelectRows(FillDown, each ([DateTemp] <> null)),
    RemoveTop = Table.Skip(Filter,1),
    Replace = Table.ReplaceValue(RemoveTop,"Class","",Replacer.ReplaceText,{"Class"}),
    Replace2 = Table.ReplaceValue(Replace,"Amount", null,Replacer.ReplaceValue,{"AmountTemp"}),
    SplitDate = Table.SplitColumn(Replace2, "DateTemp", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Date", "DateTemp.2"}),
    SubTotalLabel = Table.RemoveColumns( Table.ReplaceValue(SplitDate,"Date","Sub Total",Replacer.ReplaceText,{"Date"}), "DateTemp.2"),
    Group = Table.Group(SubTotalLabel, {"Seq"}, {{"SubTot", each List.Sum([AmountTemp]), type any}, {"All", each _, type table [Seq=number, No=nullable number, Class=nullable text, Date=nullable text, AmountTemp=any, City=text, State=text]}}),
    AddPrevSubTot = Table.AddColumn(Group, "PrevSubTot", each try Group[SubTot]{[Seq]-2} otherwise null),
    Expand = Table.ExpandTableColumn(AddPrevSubTot, "All", {"No", "Class", "Date", "AmountTemp", "City", "State"}, {"No", "Class", "Date", "AmountTemp", "City", "State"}),
    AddAmount = Table.AddColumn(Expand, "Amount", each if [AmountTemp] = null then [PrevSubTot] else [AmountTemp]),
    LastRows = 
        #table(
        { "Date", "Amount" },
        {
            { "SubTotal", List.Last( Expand[SubTot]) }, 
            { "Grand Total", List.Sum( Expand[AmountTemp]) }  
        }
    ) ,
    Combined = Table.Combine( {AddAmount, LastRows} ),
    Clean = Table.RemoveColumns( Combined, {"SubTot", "AmountTemp", "PrevSubTot" }),
    Reorder = Table.ReorderColumns(Clean,{"Seq", "State", "City", "No", "Class", "Date", "Amount"})
    
in
    Reorder