Omid Problem 35 - Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddNumeric = Table.AddColumn(Source, "Numeric", each if [Grade] = "A+" then 4 else if [Grade] = "A" then 3 else if [Grade] = "B" then 2 else 1),
    AddYearMon = Table.AddColumn(AddNumeric, "YearMo", each (Date.Year([Date])  * 10000) + Date.Month([Date]) * 10),
    Group0 = Table.Group(AddYearMon, {"Agent-id", "YearMo"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Date=datetime, #"Agent-id"=text, Grade=text, Numeric=number, YearMo=number]}}),
    AddIncr = Table.RemoveColumns( Table.AddColumn(Group0, "Increment", each Table.AddIndexColumn( [All], "Incr", 1, 1)), "All"),
    Expand0 = Table.ExpandTableColumn(AddIncr, "Increment", {"Date", "Grade", "Numeric", "Incr"}, {"Date", "Grade", "Numeric", "Incr"}),
    FilterLast = Table.RemoveColumns( Table.SelectRows(Expand0, each ([Incr] = [Count])), {"Count", "Incr"}),
    Sort0 = Table.Buffer( Table.Sort(FilterLast,{{"Agent-id", Order.Ascending}, {"Date", Order.Ascending}})),
    Group = Table.Group(Sort0, {"Agent-id"}, {{"All", each _, type table [#"Agent-id"=text, YearMo=number, Date=datetime, Grade=text, Numeric=number]}}),
    AddMon1Idx = Table.AddColumn(Group, "Mon1Idx", each Table.AddIndexColumn( [All], "Mon1Idx", 1, 1)),
    AddMon0Idx = Table.RemoveColumns( Table.AddColumn(AddMon1Idx, "Mon0Idx", each Table.AddIndexColumn( [Mon1Idx], "Mon0Idx", 0, 1)), {"All", "Mon1Idx"}),
    Expand = Table.Buffer( Table.ExpandTableColumn(AddMon0Idx, "Mon0Idx", {"YearMo", "Date", "Grade", "Numeric", "Mon1Idx", "Mon0Idx"}, {"YearMo", "Date", "Grade", "Numeric", "Mon1Idx", "Mon0Idx.1"})),
    Join = Table.Join( Expand, {"Agent-id", "Mon0Idx.1"}, Table.PrefixColumns( Expand, "J"), {"J.Agent-id", "J.Mon1Idx"}),
    Sort = Table.Sort(Join,{{"Agent-id", Order.Ascending}, {"Date", Order.Ascending}}),
    AddMove = Table.AddColumn(Sort, "Move", each 
        if [Numeric] - [J.Numeric] > 0 then "Upgrade" else 
        if [Numeric] - [J.Numeric] = 0 then "NoChange" else 
        "Downgrade"),
    RemoveOther = Table.SelectColumns(AddMove,{"YearMo", "J.Agent-id", "Move"}),
    Pivot = Table.Pivot(RemoveOther, List.Distinct(RemoveOther[Move]), "Move", "J.Agent-id", List.Count),
    AddMonth = Table.RemoveColumns( Table.AddColumn(Pivot, "Month", each Text.Range( Text.From( [YearMo] ), 6, 1)), "YearMo"),
    Reorder = Table.ReorderColumns(AddMonth,{"Month", "Upgrade", "NoChange", "Downgrade"})
in
    Reorder