Forum Discussion
Power Query: create a column with maximum date per group that meets a condition
Hi,
I'd like to know if Power Query allows to create a column with the maximum value per month that meets a certain condition.
I have the following data:
| Date | Value | Previous value |
| 21/04/2022 | 23 | 12 |
| 20/04/2022 | 12 | 12 |
| 19/04/2022 | 12 | 6 |
| 21/03/2022 | 8 | 5 |
| 20/03/2022 | 5 | 5 |
| 19/03/2022 | 5 | 4 |
| 18/03/2022 | 4 | 4 |
What I'd like to obtain is, for each month, the maximum date where Value and Previous Value are equal. In the case of April 2022 there is only one date where this condition is met, April 20th, and in the case of March 2022, we have two dates with this condition: March 20th and March 18th.
So my desired output would be the following one:
| Date | Value | Previous value | Maximum date |
| 21/04/2022 | 23 | 12 | 20/04/2022 |
| 20/04/2022 | 12 | 12 | 20/04/2022 |
| 19/04/2022 | 12 | 6 | 20/04/2022 |
| 21/03/2022 | 8 | 5 | 20/03/2022 |
| 20/03/2022 | 5 | 5 | 20/03/2022 |
| 19/03/2022 | 5 | 4 | 20/03/2022 |
| 18/03/2022 | 4 | 4 | 20/03/2022 |
Is there any way in Power Query to get Maximum date column?
Thanks for your kind help.
Hi Anonymous ,
I think I have a solution here:
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLUNzDRNzIwMlJQ0lEyMgYShkZKsTpAGQNkGaAgXMbQEkPGDKIFaJgxXMICiE3hRiHETeHiIIMg4lBhE4iwBZKwCUQ4FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t, #"Previous value" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}, {"Previous value", Int64.Type}}), #"Added DateMonthYear Column" = Table.AddColumn(#"Changed Type", "DateMonthYear", each Text.Combine({Date.ToText([Date], "yyyy"), "0", Date.ToText([Date], "%M")}), type text), #"Added EqualFlag" = Table.AddColumn(#"Added DateMonthYear Column", "EqualFlag", each if [Value] = [Previous value] then 1 else 0), #"Filtered Rows" = Table.SelectRows(#"Added EqualFlag", each ([EqualFlag] = 1)), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"DateMonthYear"}, {{"Grouping", each List.Max([Date]), type nullable date}}), #"Merged Queries" = Table.NestedJoin(#"Added DateMonthYear Column", {"DateMonthYear"}, #"Grouped Rows", {"DateMonthYear"}, "Grouped Rows", JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Grouping"}, {"Grouped Rows.Grouping"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Grouped Rows",{"DateMonthYear"}) in #"Removed Columns"Let me know if this solves your issue 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
1 Reply
Hi Anonymous ,
I think I have a solution here:
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLUNzDRNzIwMlJQ0lEyMgYShkZKsTpAGQNkGaAgXMbQEkPGDKIFaJgxXMICiE3hRiHETeHiIIMg4lBhE4iwBZKwCUQ4FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t, #"Previous value" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}, {"Previous value", Int64.Type}}), #"Added DateMonthYear Column" = Table.AddColumn(#"Changed Type", "DateMonthYear", each Text.Combine({Date.ToText([Date], "yyyy"), "0", Date.ToText([Date], "%M")}), type text), #"Added EqualFlag" = Table.AddColumn(#"Added DateMonthYear Column", "EqualFlag", each if [Value] = [Previous value] then 1 else 0), #"Filtered Rows" = Table.SelectRows(#"Added EqualFlag", each ([EqualFlag] = 1)), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"DateMonthYear"}, {{"Grouping", each List.Max([Date]), type nullable date}}), #"Merged Queries" = Table.NestedJoin(#"Added DateMonthYear Column", {"DateMonthYear"}, #"Grouped Rows", {"DateMonthYear"}, "Grouped Rows", JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Grouping"}, {"Grouped Rows.Grouping"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Grouped Rows",{"DateMonthYear"}) in #"Removed Columns"Let me know if this solves your issue 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/