Forum Discussion
Conditional List.max
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 Va...
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
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
Thanks for your help, much appreciated!