cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
candicehan
Frequent Visitor

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

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.

QuarterSales ForecastRelease Date
Q150000November 5, 2020
Q260000November 5, 2020
Q345000November 5, 2020
Q475000November 5, 2020
Q147000October 20, 2020
Q255000October 20, 2020
Q340000October 20, 2020
Q473000October 20, 2020
Q150000October 4, 2020
Q257000October 4, 2020
Q345000October 4, 2020
Q473000October 4, 2020
Q150000September 25, 2020
Q255000September 25, 2020
Q340000September 25, 2020
Q472000September 25, 2020

 

Result I am expecting

QuarterSales ForecastRelease Date
Q150000November 5, 2020
Q260000November 5, 2020
Q345000November 5, 2020
Q475000November 5, 2020

And

QuarterSales ForecastRelease Date
Q147000October 20, 2020
Q255000October 20, 2020
Q340000October 20, 2020
Q473000October 20, 2020

 


Thanks a lot!!

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

3 ACCEPTED SOLUTIONS
camargos88
Super User III
Super User III

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

 

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

Jimmy801
Super User III
Super User III

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

View solution in original post

Rocco_sprmnt21
Super User II
Super User II

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"

View solution in original post

6 REPLIES 6
V-pazhen-msft
Community Support
Community Support

@Rocco_sprmnt21 

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?

expected.JPG

 

Regards
Paul

 

Rocco_sprmnt21
Super User II
Super User II

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"

View solution in original post

Jimmy801
Super User III
Super User III

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

View solution in original post

camargos88
Super User III
Super User III

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

 

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

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

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"

 

Jimmy801_0-1608275454631.png

 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors