List Multiplication by Index Position in Power Query

In this article, you learn how to multiply values from two list by their index position in Power Query. We’ll explore several methods to improve our understanding of the M language. If you’re new to lists, make sure to read this comprehensive guide to lists.

Understanding the Concept

Consider two lists: {1, 2, 3} and {3, 6, 9}. Our goal is to multiply each element in these lists by their respective index positions, resulting in a new list: { (1*3), (2*6), (3*9) }, which simplifies to {3, 12, 27}. We assume both lists are of identical length for simplicity.

Using List.Transform and List.Positions

One effective method for multiplying elements in two lists by their index positions involves the use of List.Transform and List.Positions. This approach is shown in the following code:

// output: { 3, 12, 27 }
let 
  list1 = { 1, 2, 3 },
  list2 = { 3, 6, 9 }, 
  listPositions = List.Positions( list1 ), // returns a list of offsets
  result = List.Transform( listPositions , each list1{_} * list2{_} )
in 
  result

In this method, we start by generating a list of index positions for list1, which are {0, 1, 2}. These indices serve as a reference to access corresponding elements in both list1 and list2. The function List.Transform then applies a multiplication operation to each matched pair of elements from list1 and list2, based on their index positions. The outcome is a new list where each element represents the product of elements from list1 and list2 at the same index.

Using List.Transform and List.Zip

Another method to multiply elements in two lists by their respective index positions employs List.Zip in conjunction with List.Transform. List.Zip merges multiple lists into a single list, where each sub-list consists of elements from the original lists that correspond to the same index. The following code demonstrates this approach:

// Output: {{1, 3}, {2, 6}, {3, 9}}
let 
  list1 = { 1, 2, 3 },
  list2 = { 3, 6, 9 }, 
  listZip= List.Zip( { list1, list2 } )
in
  listZip

This code initially uses List.Zip to pair elements from list1 and list2 based on their positions, resulting in a list of pairs. Each sub-list in the resulting list contains two elements that are in the same position in their original lists.

Using this list we already have the values to multiply bundled together within the inner lists. We can pass those to List.Transform and for each sub-list, multiply the elements within the sub-lists using List.Product:

// output: { 3, 12, 27 }
let 
  list1 = { 1, 2, 3 },
  list2 = { 3, 6, 9 }, 
  listZip= List.Zip( { list1, list2 } ), 
  // Multiplies the first item by the second item for each inner-list
  result = List.Transform( listZip, List.Product )
in 
  result

Here, List.Transform iterates through each sub-list, multiplying the elements. The output is a new list where each element is the product of corresponding elements from list1 and list2 at the same index.

Using List.Zip with List.Accumulate

A further method to multiply elements in two lists by their index positions involves the use of List.Zip and List.Accumulate. This approach starts with creating a zipped list, similar to the previous example, and then accumulates the products of the paired elements. The process is as follows:

let 
  list1 = { 1, 2, 3 },
  list2 = { 3, 6, 9 }, 
  ziplist = List.Zip( { list1, list2 } ),
  result =  
    List.Accumulate( 
      ziplist, 
      {}, 
      ( state, current ) => state & { List.Product( current ) } 
    )
in 
  result

In this method, List.Zip pairs the corresponding elements from list1 and list2, forming a list of inner-lists. Then, List.Accumulate iteratively processes each inner-list in this list. In the first iteration, it multiplies the two elements of the first inner list and stores the result in a new list. The iterations that follow repeat this multiplication (using List.Product) for the remaining inner-lists, appending each new product to the growing result list. The final output is a list of products corresponding to the multiplication of matched elements from list1 and list2.

Conclusion

To conclude, there are multiple ways to multiply elements in lists by their index positions in Power Query. While each method has its benefits, I recommend keeping your code as simple as possible by primarily using List.Transform. This approach tends to be more straightforward compared to more complex methods like List.Accumulate.

Related Articles

• Removing List Items by Position
• Selecting Items from a list by Position