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
Community Champion
Community Champion

@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
Community Champion
Community Champion

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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



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

Jimmy801
Community Champion
Community Champion

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
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors