PQ Challenge 68 - Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    CompSales =  Table.Group(Source, {"Company"}, {{"Sales", each List.Sum([Sales]), type number}}),
    DateType = Table.TransformColumnTypes(Source,{{"From", type date}, {"To", type date}}),
    AddIndex = Table.AddIndexColumn(DateType, "Index", 1, 1, Int64.Type),
    AddDateList = Table.AddColumn(AddIndex, "DateList", each List.Dates( [From], Number.From([To]) - Number.From([From]) + 1, #duration(1, 0, 0, 0))),
    Expand = Table.ExpandListColumn(AddDateList, "DateList"),
    AddMonthNum = Table.AddColumn(Expand, "MonthNum", each Date.Month( [DateList] )),
    AddShortMonth = Table.AddColumn(AddMonthNum, "ShortMonth", each Text.Start( Date.MonthName([DateList]), 3)),
    RemoveWeekends = Table.SelectRows( Table.AddColumn(AddShortMonth, "AddDay", each Date.DayOfWeekName([DateList])), each not Text.StartsWith( [AddDay], "S")),
    Group = Table.Group(RemoveWeekends, {"Company", "ShortMonth", "MonthNum"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    SortMonNames = Table.Buffer( Table.RemoveColumns( Table.Sort(Group,{{"Company", Order.Ascending}, {"MonthNum", Order.Ascending}}), "MonthNum")),
    PivotMonths = Table.Pivot(SortMonNames, List.Distinct(SortMonNames[ShortMonth]), "ShortMonth", "Count"),
    JoinSales = Table.Join( CompSales, "Company", PivotMonths, "Company"),
    SortCompany = Table.Sort(JoinSales,{{"Company", Order.Ascending}}),
    Headers= Table.ColumnNames(SortCompany),
    AddRowTotal = Table.AddColumn(SortCompany, "Row Total", each List.Sum({[Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec]}), Int64.Type),
    ToCols = Table.ToColumns(AddRowTotal),
    ColSums = {"Column Totals"} & List.Transform( List.Skip( ToCols, 1), List.Sum),
    BotRowTable = Table.FromColumns( {Headers, ColSums}),
    Transpose = Table.Transpose(BotRowTable),
    Promote = Table.PromoteHeaders(Transpose, [PromoteAllScalars=true]),
    Rename = Table.RenameColumns(Promote,{{"Column15", "Row Total"}}),
    Append = Table.Combine( {AddRowTotal, Rename})
in
    Append