Forum Discussion
'Group By' operators - no PRODUCT option?
- 4 years ago
You can use whatever function you'd like with a small tweak to the code. Do the Group By using Sum and it will generate a step that looks like this:
Edit the code in the formula bar to replace List.Sum with List.Product and it will take the product over the group instead of the sum.
Sample M code you can paste into your Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYiOlWJ1oIAnhGYN5xkCWExCbgHkmUDlDMM8UKgfRZwaVA6qMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Group = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Group", type text}, {"Value", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Group"}, {{"Value", each List.Product([Value]), type nullable number}}) in #"Grouped Rows"I use this trick with other operators too. For example, Text.Combine to concatenate text rows.
You can use whatever function you'd like with a small tweak to the code. Do the Group By using Sum and it will generate a step that looks like this:
Edit the code in the formula bar to replace List.Sum with List.Product and it will take the product over the group instead of the sum.
Sample M code you can paste into your Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYiOlWJ1oIAnhGYN5xkCWExCbgHkmUDlDMM8UKgfRZwaVA6qMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Group = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Group", type text}, {"Value", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Group"}, {{"Value", each List.Product([Value]), type nullable number}})
in
#"Grouped Rows"
I use this trick with other operators too. For example, Text.Combine to concatenate text rows.