Please help with formula to add another argument

[Pbiuserr]

Hello,

I perform such action - Ive managed to group ID by max date

#"Grouped Rows" = Table.Group(#"Previous Step", {"ID"}, {{"NewTbl", each let t =_ in Table.SelectRows(t, each [CREATION DATE] = List.Max(t[CREATION DATE])), type table }})
))

However my criteria are - max date for ID, and if date is the same, then max value for the date. If it happens that date and value is the same for all lines for certain ID - just take one line for that ID 

It suppose to look like this

My initial table

ID	CREATION DATE	VALUE
ID1	01.01.2022	100
ID1	01.01.2022	150
ID1	01.02.2022	100
ID2	01.03.2022	150
ID2	01.03.2022	100
ID3	01.04.2022	300
ID3	01.04.2022	300
ID3	01.04.2022	300

Expected output

ID	CREATION DATE	VALUE
ID1	01.02.2022	100
ID2	01.03.2022	150
ID3	01.04.2022	300

Thank you in advance for your help

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRMjIwMtI1MAQiIMfQwEApVge7lCmmlBG6LiO4lDG6LkwpuC5juJQJRMqYfKlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"CREATION DATE" = _t, VALUE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"CREATION DATE", type date}, {"VALUE", Int64.Type}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type", {"ID", "CREATION DATE"}, {{"VALUE", each List.Max([VALUE]), type nullable number}}),
    #"Grouped Rows" = Table.Group(#"Grouped Rows1", {"ID"}, {{"MaxDate", each List.Max([CREATION DATE]), type nullable date}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"ID", "MaxDate"}, #"Grouped Rows1", {"ID", "CREATION DATE"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"VALUE"}, {"VALUE"})
in
    #"Expanded Grouped Rows"

[Pbiuserr]

Hi,

It works! May I ask how come you merged table? With which table? I can see that with previous one but, you did that by typing, not using ribbon options and clicking right? Because I tried to replicate that and I couldnt select previous table (with no maxdate just CREATION DATE)

[Vijay_A_Verma]

Yes, first you will have to do a self merge i.e same table to same table. Then you will need to change one part manually.