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

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

Download the file

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

MMULT in Power Query

 

Table2

MMULT in Power Query

 

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:

MMULT in Power Query

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:

SUMPRODUCT in Power Query

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.

 

Leave a Comment

Your email address will not be published.

*
*