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 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!
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
2 Replies
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