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.

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

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.

Subscribe To DataChant

Join our mailing list to receive exclusive content that was used in the preparation of this article.

Thank you for subscribing to DataChant. An email will be sent to you shortly with more details.

loop

Leave a Reply