Crispo Mwangi Power Query Challenge - Dec 3 2023 - Brian Julius Solution

gistfile1.txt

let
    Source = Table.RemoveColumns( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "Range Difference"),
    AddSortOrder = Table.AddIndexColumn(Source, "SortOrder", 0, 1, Int64.Type),
    Table2 = Table2,
    Unpivot = Table.TransformColumns( Table.UnpivotOtherColumns(Table2, {"Grade"}, "Year", "Value"), {"Year", each Text.Select(_, {"0".."9"})}),
    NamesGrades = Table.SelectColumns( AddSortOrder, {"Full Name", "Job Grade ", "Salary"}),
    Join = Table.Join(NamesGrades, "Job Grade ", Unpivot, "Grade", JoinKind.LeftOuter),
    AddDiff = Table.RemoveColumns( Table.AddColumn(Join, "Difference", each [Value] - [Salary]), {"Grade", "Value"}),
    AddNonNegDiff = Table.TransformColumnTypes( Table.SelectRows(AddDiff, each [Difference] >= 0), {"Year", Int64.Type}),
    Group = Table.Group(AddNonNegDiff, {"Full Name"}, {{"MinDiffNonNeg", each List.Min([Difference]), type number}}),
    Join2 = Table.TransformColumnTypes( Table.Join( AddDiff, "Full Name", Group, "Full Name"), {"Year", Int64.Type}),
    AddIndex = Table.AddIndexColumn(Join2, "Index", 1, 1, Int64.Type),
    AddPrevDiff = Table.SelectRows( Table.AddColumn(AddIndex, "PrevDiff", each if [Year] = 1 then 0 else Join2[Difference]{[Index] - 2}), each [Difference] = [MinDiffNonNeg]),
    AddRangeDiff = Table.AddColumn(AddPrevDiff, "Range Difference", each [MinDiffNonNeg] - [PrevDiff]),
    RemCols = Table.SelectColumns(AddRangeDiff,{"Full Name", "Job Grade ", "Salary", "Range Difference"}),
    Join3 = Table.Join( Table.SelectColumns( AddSortOrder, {"Full Name", "SortOrder"}), "Full Name", RemCols, "Full Name"),
    Clean = Table.Sort( Join3, "SortOrder")
in
    Clean