Forum Discussion

Schnauzer77's avatar
Schnauzer77
Regular Visitor
4 years ago
Solved

'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, av...
  • AlexisOlson's avatar
    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.