Forum Discussion
'Group By' operators - no PRODUCT option?
Hi, the power query 'group by' function seems very similar to pivot tables in Excel, apart from one major flaw (for me). There is no option to get the PRODUCT of the values, as opposed to the sum, average, min etc. Does anyone have a workaround where by I could get the PRODUCT of a column, and group the results by values in other columns?
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.
2 Replies
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.
Wow - that is so simple and the perfect solution for me. Thanks!
