Excel Matrix Multiplication – Replacing MMULT with Power Query

In my last Thank You post, I suggested that Matrix multiplication is not Excel’s forte. Following that post, I got a cool Power Query solution from Imke Feldmann, author of ThedBIccountant.com, that performs matrix multiplication with Power Query.

So today I decided to share with you my version of a Matrix Multiplication using Power Query. I am looking forward to hear your comments.

Before we start, if you insist on using Excel for matrix multiplication, let’s explain why you should consider the Power Query option instead of the array formula MMULT in Excel.

MMULT function in Excel requires a manual selection of the new dimensions of the matrix A*B whenever one of your matrices A or B changes their dimensions.

For example, if A is a 3×2 matrix and B is a 2×4 matrix. A*B is should be a 3×4 matrix. (If you forgot the definition of matrix multiplication go here).

Now imagine that matrix A grows by one row, so it becomes a 4×2 matrix, which should make A*B a 4×4 matrix. As a result, if you use MMULT, you should manually select the a new range of the array function, and click the CTRL+Shift+Enter to invoke it.

Using MMULT can become a tedious and repetitive manual labor, if matrix A constantly expands with new rows, or matrix B expands with new columns. I don’t envy the data analysts that need to update their workbooks each time with the necessary error-prone manual steps.

Luckily for us, we have Power Query technology in Excel and Power BI, With Power Query you can automate your matrix multiplication without the need to perform manual adjustments whenever the dimensions change.

Download this workbook to see Power Query’s version for matrix multiplication in action.
In this shared workbook we have matrix A and matrix B as two named ranges MatrixA and MatrixBMore specifically, the range B6:G9 is defined as the name range MatrixA, and the range I6:K11 os defined as MatrixB.

Screenshot_9

Now, using the Power Query M expression below, you can perform the matrix multiplication of A*B.

(In the shared workbook you get everything to start multiplying matrices, so if you are not a Power Query expert, you can skip this block of code).

You can see the result which is the matrix A*B in the shared workbook. To refresh the calculation click Data–>Refresh All, or just refresh the query MatrixAB in the Workbook Queries pane.

Note: Whenever one of the matrices change by value or dimension, refreshing the workbook will load the result.

Screenshot_11

If you are an advanced Power Query user, and want to learn how the expression above works, please let me know.

In this post I will focus only on the core transformation which uses the function List.Generate to perform the main matrix multiplication calculation of an individual cell in the matrix A*B.

Screenshot_12

In the M expression above we use the function fnMatrixProduct, which uses List.Generate to iterate over a given row from A and a column from B and calculate the single cell in A*B.

Since Power Query uses a functional language for the transformation it was not designed to perform loop iterations, well, at least not directly. So we are using List.Generate to iterate over the two input lists and generate a new list whose elements are the product of the elements in the input lists.

List.Generate function consists of 4 arguments (more here):

Argument Description
start A function that provides the initial value
condition A condition which controls the sequence of generation
next A function that generates the next value for an iteration
transfomer A transformation function to apply to each item in the list

Let’s explain how we implemented each argument here:

Part 1: The “start” argument

This argument contains the record (a Key-Value pairs which are used to define the state during the list generation). The record is used as the starting state for our loop, and can be accessed and modified at each iteration step. We assign the key A with the value of list1, and key B with list2. The last field in the record is used for the running index.

Part 2: The “condition” argument

This part contains the condition that should be met in each iteration. When the conditon is not met, the generation of the new list stops. Since Index starts with zero, we should stop generating the list when Index equal to one of the lists’ size.

Part 3: The “next” argument

This argument defines the changes we should perform on record, before the next iteration. In our case, we keep the keys A and B with the original input lists, and increment the index by 1.

Part 4: The “transformer” argument

This argument contains the main calculation step. It defines the arithmetic operation we perform when we generate the k’th element in the new list. We read the k’th values in each list and multiply them. The result is stored as a new element in the list.

The output of List.Generate is a list of products. Now we just need to apply the  part of the formula above, and for that we can use function List.Sum.

That’s it for today. On my next blog post I may go further and explain the rest of the query. But no promises 🙂

Io conclude this blog post, I will be glad to hear your feedback and learn more about your scenarios for using matrix multiplication in Excel. I am sure that the new technique is much better than the classic MMULT array formula.

Update: 6/6/2016: It turns out that the query above is relatively slow. I got faster versions from Bill Szysz, and Imke Feldmann. I have updated the faster query here, and a new blog post here with the step-by-step instructions.

Enjoy,

Gil

12 comments

  1. Pingback: Nested Loop with List.Generate in Power Query – DataChant

  2. Bill Szysz Reply

    Hi Gil.
    Nice List.Generate example.
    I am very, very sorry but I think that this is not proper way to multiply matrices because of performance. Both of your solution are very slow 🙁 Just try it for not big matrices A 10×15 and B 15×25.
    So, very good List.Generate part (as an example of using) but entire query is not acceptable for me:-((.
    I appologize for these words, but someone should turn your attention on the performance problem. And now It looks like i am this bad guy :-((

    Have a nice weekend (really)

    • DataChant Reply

      Thank you for the feedback Bill. How slow is the refresh on your computer? It shouldn’t be slow with so few rows and columns. Any chance you use a 32 bits Excel? Does Power Query usually work fast on your Excel?

      • Bill Szysz

        Thank you for your interest in the problem.
        Hardware and software: Intel i7 (4 core, 2,2 MHz), Ram 8 GB, Win 8.1 x64, Excel 2010 x86,. PQ ver. 2.32.4307.502
        Usually PQ works very fine on this machine. I can send you the file i use if you want (with your and my solution inside).

      • Bill Szysz

        It takes more than 1 minute, and the second one even more.
        I blocked these queries because it freezes my excel.
        I’ve sent the file to you just now.

      • Gil Raviv

        Thank you Bill. I updated the workbook with your query and added a new blog post to show how to create it. Looking forward to your future feedbacks and ideas.

  3. hugoberry Reply

    Hi Gil, I think you need to avoid explicitly iterating in functional languages. Nevertheless you might improve performance of this approach by buffering some of the intermediary results.
    I’ve managed to come up with a matrix multiplication function that performs quite well even on bigger tables.

    (A,B) =>
    let
    //vector multiplication
    vmul = (v1,v2) =>
    List.Accumulate(
    List.Zip({v1,v2}),
    0,
    (agg,_)=>
    agg+(_{1}*_{0}))

    in
    Table.FromRows(
    List.Accumulate(Table.ToRows(A),{},(_,ai)=>
    _& { List.Accumulate(Table.ToColumns(B),{},(_,bj)=>
    _&{ vmul(ai,bj) })
    })
    )

    https://gist.github.com/Hugoberry/2f9bb69ab3ba0c071d7cfe61e1e42102

  4. Pingback: #Excel Super Links #10 – shared by David Hager | Excel For You

Leave a Reply