Search

DataChant

Chants of big data & bigger insights

Tag

List.Generate

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])
 ),

Continue reading “Nested Loop with List.Generate in Power Query”

Advertisements

Excel Matrix Multiplication – Replacing MMULT with Power Query

In my last Thank You post, I suggested that Matrix multiplication is not Excel’s forte. Following that post, I got a cool Power Query solution from Imke Feldmann, author of ThedBIccountant.com, that performs matrix multiplication with Power Query.

So today I decided to share with you my version of a Matrix Multiplication using Power Query. I am looking forward to hear your comments.

Before we start, if you insist on using Excel for matrix multiplication, let’s explain why you should consider the Power Query option instead of the array formula MMULT in Excel.

Continue reading “Excel Matrix Multiplication – Replacing MMULT with Power Query”

Blog at WordPress.com.

Up ↑