List.Transform - List Function

List.Transform is a Power Query M function that applies a transformation function to each element of a list. The function returns a new list with the transformed values.

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

Syntax

List.Transform(
   list as list,
   transform as function,
) as list

Description

The List.Transform function returns takes a list of values input and transforms each item in the list by applying a function. It is one of the easiest ways to iterate over a list of values and if your calculation allows it, the function is preferred over the more complex List.Generate and List.Accumulate.

Examples

The List.Transform function is powerful for iterating over a list of values. Let’s delve into some practical examples to understand its versatility.

Basic Numeric Transformations

Consider a simple list of numbers: { 1, 2, 3 }. To demonstrate the basic functionality of List.Transform, we’ll start by adding 1 to each number in the list:

List.Transform( { 1, 2, 3 }, each _ + 1 ) // Output: { 2, 3, 4 }

This expression exemplifies how List.Transform can apply a specific operation—in this case, addition—to each item in a list. Similarly, you can perform other arithmetic operations, such as multiplication:

List.Transform( { 1, 2, 3 }, each _ * 2 ) // Output: { 2, 4, 6 }

Text Transformations

The List.Transform function is not confined to numeric values. It easily handles text values, enabling various string manipulations. For instance, you can capitalize each string in a list:

List.Transform( { "amy", "may", "joe" }, Text.Proper ) // Output: { "Amy", "May", "Joe" }

Or extract the first letter from each string:

List.Transform( { "amy", "may", "joe" }, each Text.Start( _, 1 ) ) // Output: { "a", "m", "j" }

Advanced Item Selection

List.Transform can also be used for more sophisticated operations, such as retrieving specific items from a list based on their index position. For instance, the expression { "a", "b", "c" }{0} selects the first item ("a") from the list.

We can combine this item selection with List.Transform to retrieve multiple items at once:

// Returns the values at index position 1, 2 and 5.
// Output: { true, false, false }
let
  listValues = { true, true, false, true, false, false, false },
  getValues = List.Transform( { 1, 2, 5 }, each listValues{_} )
in
  getValues

In this example, List.Transform iterates over a list of index positions ({ 1, 2, 5 }) and extracts the corresponding values from listValues.

The List.Transform function is capable of handling a wide range of data transformation tasks focusing on iteration. From simple arithmetic operations to complex text manipulations and item selections, it offers a straightforward syntax to apply your logic on multiple items.

Learn more about List.Transform in the following articles:

Generating Random Numbers in Power Query
Generate random numbers, letters, dates, & symbols in Power Query. Find out how to prevent duplicates & generate lists of random values. » Read more
Creating Sequences in Power Query
Create Sequences in Power Query and Simplify your Code. Learn how to generate series of numbers, text, symbols, and dates with simple functions. » Read more
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
Remove List Items by Positions in Power Query
This article shows how you can remove items from a list by their index position by using a custom function. » Read more
3 Ways to Select List Items by Position in Power Query
This article explores 3 different ways to select multiple items in your list by index position. » Read more

Other functions related to List.Transform are:

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