Max value with column condition

[Tavi_]

Hi,

In Power Query I have a table like below. I want to add another column with the max value from the combination between BUDAT and KUNNR_PRCTR__key. Can someone help me with this?

[Anonymous · Accepted solution]

Hi @Tavi_ ,

Can you explain what do you mean by "the max value from the combination between BUDAT and KUNNR_PRCTR__key"? Which value are you trying to maximize for each combination of BUDAT+KUNNR_PRCTR__key?
Here I assume you also have a column of Values, and you want to find the maximum value of Value for each different combination of BUDAT+KUNNR_PRCTR__key:

Here is the whole M function in the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdBBCsAgDETRu2QtOIla41lEev9b1I2lJTC7D2/35xTNlg1WJAmGlxsK7FZZ6YcK6weNYWFYGTaGV0CFH+wMneEIWGt7J4BqfPTVPWk9", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BUDAT = _t, KUNNR_PRCTR__key = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"BUDAT", type date}, {"KUNNR_PRCTR__key", type text}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"BUDAT", "KUNNR_PRCTR__key"}, {{"MAX", each List.Max([Value]), type nullable number}}),
    #"Merged" = Table.NestedJoin(#"Changed Type", {"BUDAT", "KUNNR_PRCTR__key"}, #"Grouped Rows", {"BUDAT", "KUNNR_PRCTR__key"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(Merged, "Table", {"MAX"}, {"MAX"})
in
    #"Expanded Table"

And the final output is as below:

If this is not what you want, could you please provide a bit of your desired outcome and calculation logic. Thanks!

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.