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

*or*

**A***changes their dimensions.*

**B**For example, if * A* is a 3×2 matrix and

*is a 2×4 matrix.*

**B***is should be a 3×4 matrix. (If you forgot the definition of matrix multiplication go here).*

**A*B**Now imagine that matrix * A* grows by one row, so it becomes a 4×2 matrix, which should make

*a 4×4 matrix. As a result, if you use*

**A*B***, you should manually select the a new range of the array function, and click the CTRL+Shift+Enter to invoke it.*

**MMULT**Using MMULT can become a tedious and repetitive manual labor, if matrix * A* constantly expands with new rows, or matrix

*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.*

**B**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

*as two named ranges*

**B***and*

**MatrixA***More specifically, the range B6:G9 is defined as the name range*

**MatrixB**.*, and the range I6:K11 os defined as*

**MatrixA***.*

**MatrixB**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).

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | let MatrixA = Excel.CurrentWorkbook(){[Name="MatrixA"]}[Content], MatrixB = Excel.CurrentWorkbook(){[Name="MatrixB"]}[Content], RowIndices = List.Numbers(0,Table.RowCount(MatrixA)), ColumnIndices = List.Numbers(0,Table.ColumnCount(MatrixB)), #"Converted to Table" = Table.FromList( RowIndices, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Row"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each ColumnIndices), #"Renamed Columns1" = Table.RenameColumns(#"Added Custom", {{"Custom", "Column"}}), #"Expanded Custom" = Table.ExpandListColumn(#"Renamed Columns1", "Column"), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Row Data", each Record.FieldValues(MatrixA{[Row]})), MatrixBColumns = Table.ToColumns(MatrixB), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Column Data", each MatrixBColumns{[Column]}), fnMatrixProduct = (list1, list2) => List.Sum( List.Generate( ()=>[A=list1,B=list2, Index=0], each [Index] < List.Count([A]), each [A=[A],B=[B],Index =[Index]+1], each [A]{[Index]}*[B]{[Index]} ) ), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom", each fnMatrixProduct([Row Data],[Column Data])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom3", {"Row Data", "Column Data"}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Column", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Column", type text}}, "en-US")[Column]), "Column", "Custom") in #"Pivoted Column" |

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

*in the Workbook Queries pane.*

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

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*.

In the M expression above we use the function * fnMatrixProduct*, which uses

*to iterate over a given row from*

**List.Generate***and a column from*

**A***calculate the single cell in*

**B**and*.*

**A*B**1 2 3 4 5 6 | fnMatrixProduct = (list1, list2) => List.Sum( List.Generate(()=>[A=list1,B=list2, Index=0], each [Index] < List.Count([A]), each [A=[A],B=[B],Index =[Index]+1], each [A]{[Index]}*[B]{[Index]})), |

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:

1 2 3 4 5 6 | List.Generate( ()=>[A=list1,B=list2, Index=0], each [Index] < List.Count([A]), each [A=[A],B=[B],Index =[Index]+1], each [A]{[Index]}*[B]{[Index]}) ), |

Part 1: The “start”* *argument

1 | ()=>[A=list1,B=list2, Index=0], |

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

*, and key*

**list1***with*

**B***. The last field in the record is used for the running index.*

**list2**Part 2: The “condition”* *argument

1 | each [Index] < List.Count([A]), |

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

1 | each [A=[A],B=[B],Index =[Index]+1], |

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

1 | each [A]{[Index]}*[B]{[Index]}) |

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**1 2 3 | fnMatrixProduct = (list1, list2) => List.Sum( List.Generate(...), |

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

ImkeFHi Gil,

your solution is better than mine – love it 🙂

DataChantThank you Imke for inspiring me to try it. Your solution was extremely creative.

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

Bill SzyszHi 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)

DataChantThank 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 SzyszThank 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).

DataChantHow much time did it take for the refresh? Office x86 would be my immediate suspect. You can send me the workbook to [email protected]

Have a wonderful weekend.

Bill SzyszIt 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 RavivThank 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.

hugoberryHi 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

Gil RavivPost authorThank you for sharing 🙂

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