Livio/ June 19, 2018/ Excel, Power Query/ 1 comments

Replicate SUMPRODUCT in Power Query

Today I stumbled upon the M function List.Zip which I thought would provide a valid alternative to my previous post  about replicating SUMPRODUCT in Power Query.

According to Microsoft Power Query help: List.Zip takes a list of lists, and returns a list of lists combining items at the same position. This means that if you have 3 lists consisting of {{x1,x2,x3,x4,x5}, {y1,y2,y3,y4,y5}, {z1,z2,z3,z4,z5}}, List.Zip will turn it into {{x1,y1,z1},{x2,y2,z2},{x3,y3,z3},{x4,y4,z4},{x5,y5,z5}}. Pretty amazing and useful stuff.

Thanks to this, we can build another function which takes in a list of lists and performs the SUMPRODUCT:

PQSumProduct = (L as list) as number => List.Sum(List.Transform(
                                                                                                  List.Zip(L),
                                                                                                  each List.Product(_)
                                                                                                 )
                                                                           )

Starting from a table like the one below, which I have called Sales, we can now easily create a SUMPRODUCT of all the columns:

let
   PQSumProduct = (L as list) as number => List.Sum(List.Transform(
                                                                                                      List.Zip(L),
                                                                                                     each List.Product(_)
                                                                                                    )
                                                                             ),

   SalesTable = Excel.CurrentWorkbook(){[Name=”Sales”]}[Content],
   SalesColumns = Table.ToColumns(SalesTable),
   SumProduct = PQSumProduct(SalesColumns)
in
   SumProduct