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