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

[candicehan]

I need to get the subset of the entire raw date.  Only the date with the most recent  and the 2nd most recent release date will be further processed.

Raw data example.

Quarter	Sales Forecast	Release Date
Q1	50000	November 5, 2020
Q2	60000	November 5, 2020
Q3	45000	November 5, 2020
Q4	75000	November 5, 2020
Q1	47000	October 20, 2020
Q2	55000	October 20, 2020
Q3	40000	October 20, 2020
Q4	73000	October 20, 2020
Q1	50000	October 4, 2020
Q2	57000	October 4, 2020
Q3	45000	October 4, 2020
Q4	73000	October 4, 2020
Q1	50000	September 25, 2020
Q2	55000	September 25, 2020
Q3	40000	September 25, 2020
Q4	72000	September 25, 2020

Result I am expecting

Quarter	Sales Forecast	Release Date
Q1	50000	November 5, 2020
Q2	60000	November 5, 2020
Q3	45000	November 5, 2020
Q4	75000	November 5, 2020

And

Quarter	Sales Forecast	Release Date
Q1	47000	October 20, 2020
Q2	55000	October 20, 2020
Q3	40000	October 20, 2020
Q4	73000	October 20, 2020

Thanks a lot!!
Now, every time, in the power query, I manually pick the dates. How can I achieve this automatically? 

[camargos88 · Accepted solution]

@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.

[Jimmy801 · Accepted solution]

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

[V-pazhen-msft]

@Anonymous 

If you want to make it like this, all 3 methods above are working, or you want it to separate the max date and second max date?

Regards
Paul

[Anonymous · Accepted solution]

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"

[Jimmy801 · Accepted solution]

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

[camargos88 · Accepted solution]

@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.

[candicehan]

Thank you so much! It worked!  What if I only want to get the 2nd most recent release date? 

[Jimmy801]

Hello @candicehan 

I adapted my code that you will get the second most recent release date

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.Sort(_, {{"Release Date", Order.Descending}}){1}, type record }}),
    #"Expanded Max2" = Table.ExpandRecordColumn(#"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