Select max rows for category

Hi Anonymous ,

I have created a sample for your reference. Please check the following steps as below.

1. Duplicate the table and add a custom column as below.

Date.Year([Date])

2. Group the table like that.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZFBDsMgDAT/wjmSsQMEju03ovz/G/XaCrVQ0wusYbQ2y3mml2QenLZUiUl1V2kl9pyuLSD9i4hfq1gYrpSNGlrsdgDxjypuoyJQaNBI7n4wESz1mWneSMVvBr0OMPA5nHnfE+005+k+iopnxuaGz1gYbuFlllDNpgLn4VGZmAVuifOKaVBl/ov446BWrkUOuTfzQ/DXBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Store_Year_Period = _t, Date = _t, Value1 = _t, Value2 = _t, Value3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store_Year_Period", type text}, {"Date", type text}, {"Value1", Int64.Type}, {"Value2", Int64.Type}, {"Value3", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "ca-IT"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each Date.Year([Date])),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Store_Year_Period", "Custom"}, {{"max date", each List.Max([Date]), type date}})
in
    #"Grouped Rows"

3. Then Merge tables like that.

4. Expand the max date column and insert a new custom column in the merged table.

=if [#"Table (2).max date"] = [Date] then 1 else 0

5. Then filter the table based on the custom column to get the excepted result. M code for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZFBDsMgDAT/wjmSsQMEju03ovz/G/XaCrVQ0wusYbQ2y3mml2QenLZUiUl1V2kl9pyuLSD9i4hfq1gYrpSNGlrsdgDxjypuoyJQaNBI7n4wESz1mWneSMVvBr0OMPA5nHnfE+005+k+iopnxuaGz1gYbuFlllDNpgLn4VGZmAVuifOKaVBl/ov446BWrkUOuTfzQ/DXBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Store_Year_Period = _t, Date = _t, Value1 = _t, Value2 = _t, Value3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store_Year_Period", type text}, {"Date", type text}, {"Value1", Int64.Type}, {"Value2", Int64.Type}, {"Value3", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "ca-IT"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each Date.Year([Date])),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Store_Year_Period", "Custom"}, #"Table (2)", {"Store_Year_Period", "Custom"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"max date"}, {"Table (2).max date"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Table (2)", "Custom.1", each if [#"Table (2).max date"] = [Date] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Table (2).max date", "Custom.1"})
in
    #"Removed Columns"

Hello Anonymous

here another faster version

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZFNDoUwCITv0rVJAW1tl+9dw3j/a8iAGtLEbtqBfBn+jiP9hLhzWhKVTJw1ahogUfBTOpcItQCJAyoGih+rrsFqCYgZtbmNikChANVM8lSEjeApM6p6MRVfFCruoOC1O/V/Z1xDX81bUjGjbAJ49YHiGijbVSFTgfM1ZtpezrZv6+eRKzdnRxKfEWrkauRwgmp+uMF5AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Store_Year_Period = _t, Date = _t, Value1 = _t, Value2 = _t, Value3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store_Year_Period", type text}, {"Date", type date}, {"Value1", Int64.Type}, {"Value2", Int64.Type}, {"Value3", Int64.Type}}),
    #"Grouped Rows" = Table.Group
        (
            #"Changed Type",
            {"Store_Year_Period"},
            {{
                "All",
                (table)=> Table.SelectRows
                    (
                        table,
                        (select)=> select[Date]=List.Max
                            (
                                table[Date]
                            )
                    ) 
            }}
        ),
    #"Expanded All" = Table.ExpandTableColumn
    (
        #"Grouped Rows",
        "All",
        {"Date", "Value1", "Value2", "Value3"}, {"Date", "Value1", "Value2", "Value3"}
    )
in
    #"Expanded All"

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy