Forum Discussion

Sherly26's avatar
Sherly26
Copper Contributor
Mar 11, 2023
Solved

Formula to pull out the most recent data

Dear all, I would like to ask for formula in excel which I can use if I would like to pull out the most recent data that I have. I have a spreadsheet containing 3 year of data – 2006, 2009 and 2012...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Mar 11, 2023

    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
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Mar 11, 2023

Sherly26 

Alternatively, you may want to try Power Query or when your Excel version supports it, use some of the newer functions like LET, BYROW, LAMBDA, VSTACK and HSTACK.

 

The attached file contains examples for both.

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 11, 2023

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

Resources