removed old rating and left only latest rating

[aam1932]

Hai all

I have data for system which they will update rating as and when,

My question, for a list of data, how can i remove the data which have the latest rating only, by if there is no update from earlier, take as that is latest data. example below (red text the latest rating)

Name Data.PFXRECNUM Data.CUSIP Data.DATE002 Data.RATING_SERVICE Data.RATE_CODE RATINGS 328 DM030122 3/31/2004 RAM AA3 RATINGS 336 DM030122 3/21/2005 RAM AA3 RATINGS 375 DM030122 3/25/2005 S&P RAM AA3 RATINGS 420 DM030122 5/16/2006 S&P RAM AA2 RATINGS 320 DM03926S 3/31/2004 RAM AA3 RATINGS 367 DM03926S 3/25/2005 S&P RAM AA3 RATINGS 470 DM03926S 6/4/2008 S&P RAM BB3 RATINGS 472 DM03926S 7/1/2008 RAM BBB3 RATINGS 473 DM03926S 6/30/2008 RAM BBB3 RATINGS 474 DM03926S 7/1/2008 S&P RAM BBB3 RATINGS 534 DM03926S 4/28/2010 S&P RAM AA3 RATINGS 306 PJ01163F 3/31/2004 MARC A RATINGS 344 PJ01163F 3/21/2005 MARC A+ RATINGS 353 PJ01163F 3/25/2005 S&P MARC A RATINGS 390 PJ01163F 4/29/2005 S&P MARC A+ RATINGS 391 PJ01163F 3/16/2005 S&P MARC A+ RATINGS 392 PJ01163F 5/19/2005 S&P MARC A RATINGS 411 PJ01163F 1/19/2006 S&P MARC A+ Thank you

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[billszysz]

Try this code below

let    Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Data.PFXRECNUM", Int64.Type}, {"Data.CUSIP", type text}, {"Data.DATE002", type date}, {"Data.RATING_SERVICE", type text}, {"Data.RATE_CODE", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Data.CUSIP"}, {{"tbl", each Table.FirstN(Table.Sort(_, {"Data.DATE002", Order.Descending}),1 ), type table}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"tbl"}),
    Result = Table.Combine(#"Removed Other Columns"[tbl])
in
    Result

 

[aam1932]

thank you so much, its works perfectly. thank