List.TransformMany

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function List.TransformMany

Understanding the List.TransformMany Function

The List.TransformMany function is a powerful function in Power Query that can be used to transform data in a variety of ways. At a high level, the function takes a list of values and applies a transformation function to each item in the list. The transformation function can return a list of values, and the List.TransformMany function will combine all of the lists into a single list of transformed values.

Here is a basic example of how the List.TransformMany function works:

let

inputList = {1,2,3},

outputList = List.TransformMany(inputList, each { _ 2, _ 3 })

in

outputList

In this example, we start with a list of integers (1, 2, and 3). We then use the List.TransformMany function to apply a transformation function to each item in the list. The transformation function is defined using the "each" keyword, which is a shorthand way of defining a function that takes a single argument. In this case, the argument is represented by the underscore (_) character, which is a placeholder for the current item in the list being transformed.

The transformation function itself returns a list of two values: the current item in the list multiplied by 2, and the current item in the list multiplied by 3. The List.TransformMany function then combines all of the lists together into a single list of transformed values, which in this case is (2, 3, 4, 6, 6, 9).

Advanced Examples of List.TransformMany

While the basic example above demonstrates the basic functionality of the List.TransformMany function, there are many more advanced ways that it can be used in Power Query. Here are a few examples:

Applying Multiple Transformation Functions

In some cases, you may want to apply multiple transformation functions to each item in a list. You can achieve this by nesting multiple List.TransformMany functions together. Here's an example:

let

inputList = {1,2,3},

outputList = List.TransformMany(

inputList,

each List.TransformMany(

{ _ 2, _ 3 },

each { _ + 1, _ – 1 }

)

)

in

outputList

In this example, we start with the same list of integers as before (1, 2, and 3). We then use the List.TransformMany function to apply a new transformation function to each item in the list. This new transformation function is actually another List.TransformMany function, which applies two additional transformation functions to each item in the list.

The first transformation function multiplies the current item in the list by 2 and then adds 1. The second transformation function multiplies the current item in the list by 3 and then subtracts 1. The List.TransformMany function then combines all of the lists together into a single list of transformed values, which in this case is (3, 1, 5, -1, 7, 3, 11, 5, 9).

Conditionally Applying Transformation Functions

In some cases, you may want to apply a transformation function to a sublist of items in a list based on a condition. You can achieve this by using the List.Select function to filter the list before applying the List.TransformMany function. Here's an example:

let

inputList = {1,2,3},

outputList = List.TransformMany(

List.Select(inputList, each _ < 3),

each { _ 2, _ 3 }

)

in

outputList

In this example, we start with the same list of integers as before (1, 2, and 3). We then use the List.Select function to filter the list to only include items that are less than 3. We then use the List.TransformMany function to apply a transformation function to each item in the filtered list. The transformation function is the same as before, multiplying the current item in the list by 2 and 3. The List.TransformMany function then combines all of the lists together into a single list of transformed values, which in this case is (2, 3, 4).

The List.TransformMany function is a powerful tool in Power Query that can simplify and automate many data transformation tasks. It allows you to apply transformation functions to each item in a list and combine the results into a single list. By understanding the M code behind the List.TransformMany function, you can take your data transformation skills to the next level and unlock new possibilities for data analysis and business intelligence.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)