Replicate Excel MMULT in Power Query
When you start getting into Power Query and the M language you realize the immense benefits this tool provides you with. Even the VBA geeks like myself realize that, when it comes to importing and transforming data for analysis, Power Query can save you many lines of code. MMULT in Power
In the example below I will show how I replicated the Excel MMULT for a project I was working on.
Start with your source Tables
We know that matrix multiplication can only happen when the columns of the first table equal the rows of the second table. For a Table1 of size m x n and a Table2 of size n x t, the result will be a table m x t.
For this demonstration I set up two tables, named respectively Table1 and Table2:
Table1
Table2
Building your Power Query
The main query simply imports both Table1 and Table2 and then calls the custom function PQ_MMULT which will return the matrix multiplication table:
let
Table1 = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
Table2 = Excel.CurrentWorkbook(){[Name=”Table2″]}[Content],
ResTable = PQ_MMULT(Table1,Table2)
in
ResTable
MMULT in Power Query
Custom PQ_MMULT function
The PQ_MMULT function is shown below (click on the image to see in a larger format)
You pass two arguments to the function which are Table1 and Table2 and it will return a Table which is the result of the MMULT. I have commented all the lines so you can understand what is going on at each step:
The bulk of the work is done by the List.Generate function , which at each pass is calling another custo function which replicates Excel SUMPRODUCT function shown below:
List.Generate is a power function which allows you to perform iterations in Power Query and which you should definitely get familiar with if you want to bring your queries to the next level.
List.Generate returns a Power Query list and accepts 4 arguments:
The first argument is the initial “state”, the values that your result list will have in the first position. The result list is not limited to a simple list but can also be a list of lists, or a list of table, or a list of records, basically a list of whatever you want to return. In my example above, the PQ_MMULT function is using a list of records as first argument: [i=0,j=0,Res=””,C=””,R=””]
The second argument is a boolean (or as called in Power Query: type logical) argument which must return either TRUE or FALSE, and represents the condition which tells List.Generate to stop iterating (iterate while the condition equal TRUE). This means you need to be careful in setting this argument correctly or you may generate an infinite loop.
The third argument is where the job gets done, it is where, based on the calculations you want to perform, the next items of the list which will be generated will get values assigned.
The fourth optional argument, is the items you want to return from the list that was passed in the first argument. When you are passing a simple List you can omit this argument, but if for instance you start off with a Record, the result will be a list of Records, and you may not want to return all the fields of the records. Using the PQ_MMULT example, even though I am starting with this record [i=0,j=0,Res=””,C=””,R=””], I want my result list to only include the fields R, C and Res.