Nested Loop with List.Generate in Power Query

Recently I shared a Matrix Multiplication Power Query technique here, and explained how to use List.Generate as the core step of the multiplication.

It was fun to experiment with List.Generate and use it to implement a kind of a For-Loop. So I thought it would be cool to use List.Generate to iterate over a nested loop.

Below you can find the function that I created. You can use it to implement a kind-of-a-nested-loop in Power Query.

The arguments RowCount  and ColumnCount represent the number of iterations for the outer and inner loops, respectively. To better explain my code, you can think of it as a function that performs operations on all the cells in a table, starting with the first row, iterating over the cells of each column in the specific row, then jumping to the next row, and so forth.

fnIterateNestedLoop = (RowCount, ColumnCount, fnTransformByIndices) =>
 List.Generate(
  ()=>[i=0, j=0],
  each [i] < RowCount,
  each
   if [j] < ColumnCount - 1 then
    [i=[i], j=[j]+1]
   else
    [i=[i]+1, j=0],
  each fnTransformByIndices([i], [j])
 ),

You can see in the code above that we use a record that hold the indices i (outer loop) and j (inner loop), and depending on the location of j we update the indices for the next step.

The output of the function above is a single list with all the calculations, but it is relatively easy to transform it into a table if needed.

For example, an output of {1,2,3,4,5,6,7,8,9,10,11,12} with 4 rows and 3 columns should be reconstructed as the following table

Finally, here is another version for the M expression for the matrix multiplication that was introduced here, this time we use our shining new nested loop function to iterate over all the cells that are needed for the matrix A*B.

Note: The following solution is intended for learning purposes only. Don’t use this code for real business scenarios, the query is extremely slow….

let
 
 MatrixA = Excel.CurrentWorkbook(){[Name="MatrixA"]}[Content],
 MatrixB = Excel.CurrentWorkbook(){[Name="MatrixB"]}[Content],
 
 RowCount = Table.RowCount(MatrixA),
 ColumnCount = Table.ColumnCount(MatrixB),

 RowIndices = List.Numbers(0, RowCount ),
 ColumnIndices = List.Numbers(0, ColumnCount),

 fnIterateNestedLoop = (RowCount, ColumnCount, fnTransformByIndices) =>
  List.Generate(
   ()=>[i=0, j=0],
   each [i] < RowCount,
   each
    if [j] < ColumnCount - 1 then
     [i=[i], j=[j]+1]
    else
     [i=[i]+1, j=0],
   each fnTransformByIndices([i], [j])
 ),

 
 MatrixBColumns = Table.ToColumns(MatrixB),
 
 // This function we explained in our previous blog post
 fnMatrixProduct = (i, j) =>
  List.Sum(
   List.Generate(
    ()=>[row=Record.FieldValues(MatrixA{i}), col=MatrixBColumns{j}, k=0],
    each [k] < List.Count([row]),
    each [row=[row],col=[col],k=[k]+1],
    each [row]{[k]} * [col]{[k]})),

 // Here we perform the nested loop that will generate a list of all the cells
 // of matrix A*B
 ListMatrixAxB = fnIterateNestedLoop(RowCount, ColumnCount, fnMatrixProduct),
 
 #"Converted to Table" = Table.FromList(
  ListMatrixAxB, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 
 // We add an index for each element starting from zero
 #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1),
 
 // Row index is calculated using Number.IntegerDivide
 // Row index =  index / ColumnCount
 #"Calc Row" = Table.AddColumn(
  #"Added Index", "Row", each Number.IntegerDivide([Index], ColumnCount)),
 
 // Column index is calculated  using Number.Mod
 // Column index = index % ColumnCount
 #"Calc Column" = Table.AddColumn(
  #"Calc Row", "Column", each Number.Mod([Index],ColumnCount)),
 
 #"Removed Columns" = Table.RemoveColumns(#"Calc Column",{"Index"}),
 #"Reordered Columns" = Table.ReorderColumns(
  #"Removed Columns",{"Row", "Column", "Column1"}),
 
 #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",
  {{"Column1", "Value"}}),
 
 // The main pivot operation that reconstructs the table
 #"Pivoted Column" = Table.Pivot(
  Table.TransformColumnTypes(#"Renamed Columns",
   {{"Column", type text}}, "en-US"),
   List.Distinct(Table.TransformColumnTypes(#"Renamed Columns",
    {{"Column", type text}}, "en-US")[Column]), "Column", "Value")
in
 #"Pivoted Column"

You can download this workbook to inspect the two matrix multiplication expressions side by side.

Do you have any scenarios to share where nested loop can be helpful? You should note that there are usually easier and probably faster ways to iterate over your data, but I am curious if you find cases where the code above can be useful. Please share in the comments below.

Wish you the best nested loops 🙂

Update (June 4th, 2016): The following solution is intended for learning purposes only. Don’t use it for real business scenarios-  The query is extremely slow…

Update (June 7th, 2016): I updated the workbook here  with a faster query. You can read my blog followup post here where I share a step-by-step tutorial to build the faster query.

Subscribe to DataChant to gain access to Nested Loop Example.pbix file that iterates over a table using the nested loop technique, and returns the sum of all values.

[rad_rapidology_inline optin_id=optin_5]