grouping 5 data columns by Max, with month of max

[Anonymous]

My query has the following data:

Account Name	Month	Value 1	Value 2	Value 3	Value 4	Value 5
Company A	7/1/2020	5	3	2	11	10
Company A	8/1/2020	12	40	55	22	44
Company A	9/1/2020	2	60	33	37	55
Company A	10/1/2020	19	33	20	50	22
Company A	11/1/2020	45	22	44	11	7
Company A	12/1/2020	4	11	770	900	3
Company A	1/1/2021	7	1	33	7	8
Company A	2/1/2021	90	9	63	4	98
Company A	3/1/2021	10	4	4	4	8
Company A	4/1/2021	22	8	6	8	4
Company A	5/1/2021	40	8	78	99	7
Company A	6/1/2021	2	4	9	22	8
Company B	7/1/2020	5	3	2	11	10
Company B	8/1/2020	12	40	55	22	44
Company B	9/1/2020	2	60	33	777	464
Company B	10/1/2020	19	33	20	50	22
Company B	11/1/2020	45	22	44	11	7
Company B	12/1/2020	4	11	77	9	3
Company B	1/1/2021	7	1	33	7	8
Company B	2/1/2021	123	599	63	4	98
Company B	3/1/2021	10	4	4	4	8
Company B	4/1/2021	22	8	6	8	4
Company B	5/1/2021	40	8	78	99	7
Company B	6/1/2021	2	4	9	22	8

The end result I need is the Max value for each of the 5 value columns for each Account, but I also need the Month of the Max value for each Value column.  I've tried the Grouping option and it can give me the max for all 5 broken down by Account Name, but I can't figure out how to get it to show the Month field related to the Max value for each of the 5 value columns, per Account Name.

Any suggestions?

[mahoneypat · Accepted solution]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZI9c8MgDIb/io85d0GAjRmjzu3YxZfBl8tdh8bukAz590VgPlq1aRkwku95JQHvNInD6bTelmv3Ml/OYiee1+X65vfX+f127iBHKkc6RyZHvTjuJvG0Xj7m5d4d/H+7h72SSvqw94tEVAKoIkiGjwUH4kwQklKF1DCFKwoiBgo0tdE2Sr8LQFY9XKLjhDI2YhIoElMPsx3EcoWqFBmzlDoZRuSSqIj1fJomo2RktCq0C1Xp7Hrr5jivCw9pqLQ4bQodjjpS9W3nT9AXOrwXUZY+zv14OUNVPU1cOtU0tjkImx2EjxxkLd29Gbik0UPY7CH83UPbbWnO/9tA+NVAoAjr3SMPYZOHsMlD2Ogh/NtDx08=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Month", type date}, {"Value 1", Int64.Type}, {"Value 2", Int64.Type}, {"Value 3", Int64.Type}, {"Value 4", Int64.Type}, {"Value 5", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Account Name", "Month"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Account Name", "Attribute"}, {{"AllRows", each _, type table [Account Name=nullable text, Month=nullable date, Attribute=text, Value=number]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"AllRows", each Table.Max(_, {"Value"})}}),
    #"Expanded AllRows" = Table.ExpandRecordColumn(Custom1, "AllRows", {"Month", "Value"}, {"Month", "Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded AllRows",{{"Month", type date}, {"Value", Int64.Type}})
in
    #"Changed Type1"

Pat

[Anonymous]

Thanks Pat, that did it!!

[amitchandak]

@Anonymous ,

Column like this for each column ?

flag =
var _max = maxx(filter(Table, [Account Name] = earlier([Account Name])), [Value1])
return
if([Value1] =_max, true(), false())