Conditional List.max

[Anonymous]

Hi,

I have a problem where I want to add maximum values from a column (Value) based on conditional criteria from another column (Year.week & Name). My data looks like:

Year.week & Name     Value

2019-01; Name1            3

2019-01; Name1            0

2019-01; Name1            0

2019-01; Name2            4

2019-01; Name2            0

2019-01; Name2            0

I would like my output to be like:

Year.week & Name     Value

2019-01; Name1            3

2019-01; Name1            3

2019-01; Name1            3

2019-01; Name2            4

2019-01; Name2            4

2019-01; Name2            4

I've tried to merge an if statement with list.max, use fill down function or group but can't get my output the way I want. Help would be much appreciated!

Thnx!

[Anonymous · Accepted solution]

Hi @Anonymous ,

I think the below is what you are after:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ1MLRW8EvMTTVU0lEyVorVwSJsQLSwEVDYBLswDtVA4VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Year.week & Name" = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year.week & Name", type text}, {"Value", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Year.week & Name"}, #"Changed Type", {"Year.week & Name"}, "Changed Type", JoinKind.LeftOuter),
    #"Aggregated Changed Type" = Table.AggregateTableColumn(#"Merged Queries", "Changed Type", {{"Value", List.Max, "Max of Value"}})
in
    #"Aggregated Changed Type"

Kind regards,

JB

[Anonymous]

Thanks for your help, much appreciated!