filter multiple rows of same ID by max date

[Amarinho777]

Hello,

I have a table with many columns, one of them is an ID and one of them contains dates. An ID can have multiple rows. I would like the table to show only the row of an ID with the max date.

So after applying the Power Query step(s), I would like the above table to only showthe rows in green.

Thanks in advance!

[m_alireza · Accepted solution]

Hi @Amarinho777 ,

Please see below query. You can copy the query and place it in advanced editor, taking the parts you need to recreate it in your table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZYwxCoAwDAD/0rmQNFhbHyA4io7iIBJE0ApV/2/TSXQ87rhhUNZapRVZMAYITZWgnzY+1ai/kjBB02XjnBPjwKAYSlCHZQ3McQ1LTrz3khSApZzl1MZjviPvHK53QoD5/0vGBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Department = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"Department", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"MaxDate", each List.Max([Date]), type date}, {"All", each _, type table [ID=nullable number, Date=nullable date, Department=nullable text]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Date", "Department"}, {"Date", "Department"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded All", each ([MaxDate] = [Date])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"MaxDate"})
in
    #"Removed Columns"

 I also linked a sample pbix file for your reference: 
https://drive.google.com/file/d/1YJmSFV6E51eHyi942K4jSdVh7atwmSQw/view?usp=sharing 

Sample output: