Formula to pull out the most recent data

Riny_van_Eekelen 

PQ variant

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
        Source, {"Department"},
        "Year", "Number"),
    declareTypes = Table.TransformColumnTypes(
        #"Unpivoted Other Columns",
        {
            {"Year", Int64.Type},
            {"Number", Int64.Type}
        }),
    #"Sorted Rows" = Table.Sort(
        declareTypes,
        {
            {"Department", Order.Ascending},
            {"Year", Order.Descending}
        }),
    fixInMemory = Table.AddIndexColumn(
        #"Sorted Rows", "Index", 0, 1, Int64.Type),
    removeDuplicates = Table.Distinct(fixInMemory, {"Department"}),
    removeIndex = Table.SelectColumns(
        removeDuplicates,
        {"Department", "Number", "Year"})
in
    removeIndex