List.TransformMany - List Function

List.TransformMany is a Power Query M function that projects elements from an input list using two transformation functions: collectionTransform and resultTransform. The function returns a new list with the final transformed results.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Description

The List.TransformMany function in Power Query is designed to handle complex transformations by projecting elements from an input list into a new list with possibly altered structure or content. This function works in two main stages, leveraging the collectionTransform and resultTransform functions.

collectionTransform: Transforms each element of the input list into an intermediate list. It has a signature of (x as any) as list => ..., where x is an element in the input list. Each element of the input list is passed to this function, which then returns a new list derived from or related to the original element. This process results in an intermediate list for each element in the input list.
resultTransform: Constructs the final result from the original element and the corresponding intermediate list. It’s signature is (x as any, y as any) as any => ..., where x is an element from the original list, and y is an element from the intermediate list generated in the first stage. This function combines or relates the original element with each element in its corresponding intermediate list to create the final output. It’s called for each combination of an original element and an intermediate element.

Examples

The List.TransformMany function allows you to perform complex transformations on lists. It supports multiple operations on list items, often leading to efficient code. Let’s explore some examples to showcase its utility.

Creating Custom Date Strings

Imagine you have a list of numbers from 1 to 5 and you want to transform these into custom date strings. List.TransformMany can handle this elegantly. First, it converts the numbers into dates, and then it uses a secondary transformation to format these dates:

//Returns { "1 - Jan", "2 - Feb", "3 - Mar", "4 - Apr","5 - May" }
List.TransformMany(
     { 1..5 },                   
     ( x )    => { #date( 2024, x, 1 ) },  
     ( x, y ) => Text.From( x ) & " - " & Date.ToText( y, "MMM" ) )
)

This allows you to return very specific output, while building onto the List.TransformMany function. The resultTransform is very similar to the selector argument of List.Generate.

Formatting Dates with Contextual Information

Consider a scenario where you have a list of years, and you want to generate a string that describes the day of the week for the first day of each year. List.TransformMany allows you to do this:

// Returns { "First day 2021: Friday", "First day 2022: Saturday", 
//           "First day 2023: Sunday" }
List.TransformMany(
     { 2021..2023 },
     ( x )     => { #date( x, 1, 1 ) },
     ( x, y )  => "First day " & Text.From( x ) & ": " &
                Date.DayOfWeekName( y )
  )

Flattening Nested Structures

List.TransformMany can also handle complex data structures, such as nested lists. For example, consider a list of records, each containing a person’s name and a list of their pets. To flatten this structure into a more straightforward list of individual records, you can use List.TransformMany:

List.TransformMany( 
    {
        [Name = "Alice", Pets = {"Scruffy", "Sam"}],
        [Name = "Bob", Pets = {"Walker"}]
    },
    each [Pets],
    ( person, pet ) => [Name = person[Name], Pet = pet]
 )

 /* Output: 
{
    [Name = "Alice", Pet = "Scruffy"],
    [Name = "Alice", Pet = "Sam"],
    [Name = "Bob", Pet = "Walker"]
}
 */

This highlights how List.TransformMany can be used to simplify complex data structures, making them easier to work with.

Learn more about List.TransformMany in the following articles:

Lists in Power Query M / List Functions (200+ Examples)
The complete guide to Lists in Power Query M. Learn from practical examples and master Power Query’s most powerful List functions. » Read more

Other functions related to List.TransformMany are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/list-transformmany