Livio/ June 13, 2018/ Excel, Power Query/ 0 comments

Replicate SUMPRODUCT in Power Query

In my previous post, MMULT in Power Query, I showed how to recreate Excel SUMPRODUCT using List.Generate. This post will show an alternative method using List.Accumulate.

Power Query does not come with a native function that simulates Excel SUMPRODUCT function. This is a fair easy task though using List.Accumulate function. 

Create a Blank Query, go to the Advanced Editor and add the below M code:

(L1as list, L2 as list) as number =>

let
    Result = List.Accumulate(List.Positions(L1),
                                             0,
                                             (state, current) => state + L1{current} * L2{current})
in
  Result

Then name the query PQSumProduct and save it. 

The job is basically done. You have a ready to use function which can be called from your other queries. For example:

let
    List1 = {1,4,3,5},
    List2 = {2,5,10,10},

    Result = PQSumProduct(List1,List2)

in
    Result

If you do not wish to create a separate query to hold the function you can also merge everything into one query:

let
   PQSumProduct = (L1 as list, L2 as list) as number => List.Accumulate(List.Positions(L1),
                                                                                                      0,
                                                                       (state, current) => state + L1{current} * L2{current}),
   List1 = {1,4,3,5},
   List2 = {2,5,10,10},

   Result = PQSumProduct(List1,List2)
in
   Result

Me

Leave a Comment

Your email address will not be published.

*
*