Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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"
@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
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"
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
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.
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"
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.