filter multiple rows of same ID by max date

Question

Hello,&nbsp;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.&nbsp;&nbsp;So after applying the Power Query step(s), I would like the above table to only showthe rows in green.&nbsp;Thanks in advance!

m_alireza · Accepted Answer

Hi&nbsp;Amarinho777&nbsp;,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"&nbsp;I also linked a sample pbix file for your reference:&nbsp;https://drive.google.com/file/d/1YJmSFV6E51eHyi942K4jSdVh7atwmSQw/view?usp=sharing&nbsp;Sample output:&nbsp;

Forum Discussion

filter multiple rows of same ID by max date

1 Reply

Related content

Filter rows

row filter with multiple clauses

Issue with Formulas - When Multiple Rows Similar Rows Exist

Make rows appear in multiple filter values

Multiple Years Filter