How to dynamically filter out the raw data -only pick the data with the most recent release data

candicehan ,

You can use this code to create a custom column and filter by true values:

let _lst = List.MaxN(
    List.Distinct(#"Changed Type with Locale"[Release Date]), 2),
_date = [Release Date] in 
    List.MatchesAny(_lst, each _ = _date)

Just change the #"Changed Type with Locale" by the last step before the new column.

Hello candicehan 

use Table.Group that take the whole grouped table, sorts it and takes the first 2 rows. With this approach you are cleansing all data, meaning you don't have to work with all data after the function is applied

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdExCoAwDIXhq0jnDjEm9haKOBYXpaMoIp7fWBGK2rh0yDf8PL03XWmsYZBP3mY5wjyGrWBbICCYwYpAudSqqORCrAmSi1PF1UHuFu20LxdAeHcwayJ2gCZiR6WJdI9H0CfDKSBd4xf8RVCuoQ/rfs+Fn7/yrJEz6R45E2Mwb4YT", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quarter = _t, #"Sales Forecast" = _t, #"Release Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Quarter", type text}, {"Sales Forecast", Int64.Type}, {"Release Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Quarter"}, {{"Max2", each Table.FirstN(Table.Sort(_, {{"Release Date", Order.Descending}}),2), type table [Quarter=text, Sales Forecast=number, Release Date=date]}}),
    #"Expanded Max2" = Table.ExpandTableColumn(#"Grouped Rows", "Max2", {"Sales Forecast", "Release Date"}, {"Sales Forecast", "Release Date"})
in
    #"Expanded Max2"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdExCoAwDIXhq0jnDjEm9haKOBYXpaMoIp7fWBGK2rh0yDf8PL03XWmsYZBP3mY5wjyGrWBbICCYwYpAudSqqORCrAmSi1PF1UHuFu20LxdAeHcwayJ2gCZiR6WJdI9H0CfDKSBd4xf8RVCuoQ/rfs+Fn7/yrJEz6R45E2Mwb4YT", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quarter = _t, #"Sales Forecast" = _t, #"Release Date" = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Quarter", type text}, {"Sales Forecast", Int64.Type}, {"Release Date", type date}}),
    #"Raggruppate righe" = Table.Group(#"Modificato tipo", {"Quarter"}, {{"TopTwo", each Table.MaxN(_, "Release Date",2)}}),
    #"Tabella TopTwo espansa" = Table.ExpandTableColumn(#"Raggruppate righe", "TopTwo", {"Sales Forecast", "Release Date"}, {"TopTwo.Sales Forecast", "TopTwo.Release Date"})
in
    #"Tabella TopTwo espansa"