List.TransformMany Function in Power Query

List.TransformMany is a Power Query M function that:

• Iterates through each element in a list.
• Applies a collection transform function that returns a list of related elements for each original element.
• Then applies a result transform function to each pair (original element, sub-element) to produce the final value.
• Finally, flattens all results into a single list.

Syntax

List.TransformMany(
    list as list, 
    collectionTransform as function, 
    resultTransform as function
) as list    

The function has the following parameters:

• list: The original list to transform.
• collectionTransform: A function that takes each item and returns a list (like expanding).
• resultTransform: A function that combines or reshapes the original item and the result(s) of the transformation.

The collectionTransform function transforms each element into an intermediate list, and the resultTransform function receives the original element as well as an item from the intermediate list to construct the result.

The collectionTransform function has the signature (x as any) as list => ..., where x is an element in list. The resultTransform function projects the shape of the result and has the signature (x as any, y as any) as any => ..., where x is an element in list and y is an element from the list generated by passing x to collectionTransform.

Example: Basic transformation using List.TransformMany.

Power Query M

List.TransformMany(
    {"A", "B"},
    each {"x", "y"},
    (original, transformed) => original & transformed
)      

The output of the above code is:

Explanation:
For A: {"x", "y"} → combined with A → {"Ax", "Ay"}
For B: {"x", "y"} → {"Bx", "By"}
Flattened into one list.

Example: Flattening nested lists with OrderID and Items.

Power Query M

let
    // Original list of orders with nested items
    orders = {
        [OrderID = 1, Items = {"Pen", "Book"}],
        [OrderID = 2, Items = {"Pencil"}]
    },

    // Use List.TransformMany to flatten OrderID with each Item
    FlattenedList = List.TransformMany(
        orders,
        each [Items],
        (order, item) => [OrderID = order[OrderID], Item = item]
    )
in
    FlattenedList   

The output of the above code is:

{
    [OrderID=1, Item="Pen"],
    [OrderID=1, Item="Book"],
    [OrderID=2, Item="Pencil"]
}        

Detailed Breakdown

1. Original list:

   orders = {
       [OrderID = 1, Items = {"Pen", "Book"}],
       [OrderID = 2, Items = {"Pencil"}]
   }

2. List.TransformMany signature: List.TransformMany(list, collectionTransform, resultTransform)
3. For each element of orders (records):
   • collectionTransform → each [Items]
   • Take the record → return the value of its Items field (which is a list).
   • For [OrderID = 1, Items = {"Pen","Book"}], it returns {"Pen","Book"}.
   • For [OrderID = 2, Items = {"Pencil"}], it returns {"Pencil"}.
4. How resultTransform is applied:

   (order, item) => [OrderID = order[OrderID], Item = item]

   • order: the original record (e.g., [OrderID = 1, Items = {"Pen","Book"}])
   • item: one element from the returned list (e.g., "Pen" or "Book").
5. Flattening: Finally, List.TransformMany flattens all results into a single list.

Who does the flattening?
The flattening is done by List.TransformMany itself, not by each [Items].
each [Items] just tells which sublist to iterate over for each record.

✅ This is very useful for flattening nested structures like expanding sublists or working with nested records in Power Query.