How list.transform in power query works?

List.Transform function in Power Query (M language) is used to apply a specified transformation to each item in a list and produce a new list containing the results. It’s very similar to the concept of “map” in many programming languages, where you apply a function to each item in a collection.

Basic Example of List.Tranform

Let’s start with a simple example to demonstrate how List.Transform works:

Suppose you have a list of numbers and you want to double each number. Here’s how you can use List.Transform to achieve this:

{1, 2, 3, 4, 5}

Using List.Transform

let
    // Original list of numbers
    OriginalList = {1, 2, 3, 4, 5},

    // Applying List.Transform to double each number
    DoubledList = List.Transform(OriginalList, each _ * 2)
in
    DoubledList

Explanation:

• OriginalList is your list of numbers.
• List.Transform is applied to OriginalList.
• each _ * 2 is a function applied to each item in the list. The underscore _ is a placeholder representing each item in the list. This function doubles each item.

The result of this code would be:

{2, 4, 6, 8, 10}

More Practical Example with Power Query

Now let’s use a more practical example involving a table of data. Imagine you have a table with a column of prices, and you want to apply a 10% discount to each price.

Example Data

ID	Price
1	100
2	200
3	300

Power Query Script to Apply Discount

let
    // Create a sample table
    Source = Table.FromRecords([
        [ID = 1, Price = 100],
        [ID = 2, Price = 200],
        [ID = 3, Price = 300]
    ]),

    // Add a column with discounted prices
    DiscountedPrices = Table.AddColumn(Source, "Discounted Price", each [Price] * 0.9)
in
    DiscountedPrices

In this scenario, if you had a separate list of discount factors for each price and wanted to apply each corresponding discount, you would use List.Transform. Here’s an adjusted example for that case:

let
    // Create a sample table
    Source = Table.FromRecords([
        [ID = 1, Price = 100],
        [ID = 2, Price = 200],
        [ID = 3, Price = 300]
    ]),

    // List of discount factors
    Discounts = {0.9, 0.85, 0.8},

    // Applying discounts to each price using List.Transform
    ApplyDiscounts = List.Transform(Source[Price], each _ * List.FirstN(Discounts, 1)),
    // Creating a new table with discounted prices
    DiscountedTable = Table.FromColumns({Source[ID], ApplyDiscounts}, {"ID", "Discounted Price"})
in
    DiscountedTable

Explanation:

• Source[Price] extracts the prices into a list.
• Discounts is a list of discount factors.
• List.Transform is used to apply the corresponding discount factor to each price. However, this example assumes the same discount for demonstration simplicity. Typically, you’d need to pair discounts and prices more carefully, perhaps using List.Zip to combine lists into pairs if they’re meant to interact item-wise.

These examples should give you a clearer understanding of how List.Transform works in different contexts within Power Query.