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

 

Screenshot_17

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.

Screenshot_18.png

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]

loop

4 comments

  1. Yury Suturin Reply

    Hi Gil.
    Big fan if your book – reading it all the time and using in my work. By any chance could you give your opinion in next:
    I have a big data sheet where I need to keep only specific rows. I used the functions Table.SelectRows where I check the rows numbers vs values in the list, and it’s working fine, but I can’t figure out how to source that List from the cell in a worksheet. Currently I use the list as manual entry.
    I am trying to build a list from the values in cell which look like this – 1,2,4..7,15-40. That cell is just a cell in Excel worksheet, and I have no problems with loading it into Query editor but after that I kind of hit the wall. Tried different functions like Table.ToList and others but without any luck. Then I split the cell into different rows and trying to build list from the cells with “..” by using List.Generate but it’s not working as I expected – just gives back errors.
    By any chance you know how to deal with this one or am I on the wrong track?
    Thanks so much.

Leave a Reply