Understanding Lambda Functions in Power Query

Lambda functions in Power Query are a powerful feature introduced to make it easier to perform complex operations on data without needing to create multiple helper columns or complex nested functions. Let’s start with some basic concepts and then move on to a practical example.

What is a Lambda Function?

A Lambda function is essentially an anonymous function (a function without a name) that can be defined and called on the fly within expressions in Power Query. It allows you to define a function inline and pass parameters to it, executing complex data transformations succinctly and efficiently.

Why Use Lambda Functions?

Lambda functions are particularly useful when you need to:

• Apply a custom operation repeatedly across multiple values or columns.
• Implement complex logic that would otherwise require multiple steps or auxiliary functions.
• Make your code cleaner and more readable by avoiding repetitive code.

Step-by-Step Example with Test Data

Let’s go through a practical example to see how Lambda functions work. First, we will create some test data.

Creating Test Data

Imagine we have a dataset of sales transactions, and we want to analyze the data for insights. Here’s a simplified version of such data:

Transaction ID | Product | Quantity | Price per Unit
---------------|---------|----------|---------------
1              | Apple   | 3        | 1.50
2              | Banana  | 5        | 0.80
3              | Carrot  | 2        | 0.60
4              | Apple   | 1        | 1.50
5              | Banana  | 3        | 0.80

Applying Lambda Functions in Power Query

Goal: Calculate the total price for each transaction and then determine the average price per product.

Step 1: Load the Data into Power Query

• For our example, assume this data is already loaded into Power Query.

Step 2: Add a Custom Column for Total Price

• We need to calculate the total price for each transaction.
• Normally, you might just multiply the Quantity by Price per Unit. With a Lambda function, we can encapsulate this logic inside a function.

Lambda Function for Total Price:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {{"Product", type text}, {"Quantity", Int64.Type}, {"Price per Unit", type number}}
    ),
    #"Added Total Price" = Table.AddColumn(
        #"Changed Type", 
        "Total Price", 
        each ((quantity, price) => quantity * price)([Quantity], [Price per Unit])
    )
in
    #"Added Total Price"

Explanation of the Code with Lambda Function

1. Source and Type Change:
   • These steps remain unchanged, setting up your data by retrieving it and ensuring the correct types for calculations.
2. Adding the Total Price Column with a Lambda Function:
   • #"Added Total Price" = Table.AddColumn(...)
     • In this step, you add the “Total Price” column.
     • The expression now includes a Lambda function: ((quantity, price) => quantity * price)([Quantity], [Price per Unit]).
     • Here’s the breakdown:
       • ((quantity, price) => quantity * price): This is the definition of a Lambda function where quantity and price are parameters.
       • The function takes two arguments and returns their product. The parameters quantity and price are placeholders that will be replaced by the actual values from each row.
       • [Quantity] and [Price per Unit] are passed as arguments to this Lambda function for each row, thanks to the each keyword.

How This Works

• Lambda Syntax: The => in the expression defines the function, separating the input parameters (quantity, price) from the function body (quantity * price), which computes the product.
• Application to Each Row: By wrapping the Lambda function call within an each expression, you tell Power Query to apply this function to each row individually, substituting [Quantity] and [Price per Unit] from each row into the function.

Applying the Changes

After you’ve edited the script in the Advanced Editor:

• Click “Done” to close the editor and view the table in Power Query’s main interface.
• Verify that the new column “Total Price” appears correctly with expected values.
• Click “Close & Load” to finalize the changes and load the enhanced data back into Excel.

This setup not only shows how Lambda functions can be utilized in Power Query but also improves the readability of your code by clearly defining operations performed on each data row.

Conclusion: Empower Your Data Analysis

Lambda functions are not just a feature—they are a shift towards more efficient and powerful data analysis in Power BI. By incorporating Lambdas into your workflows, you harness the full potential of Power Query, turning complex data transformations into straightforward tasks.

Encouraging Further Exploration

For those eager to further explore Lambda functions, consider applying them to more complex datasets or combining them with other Power Query features like conditional statements and loops. Each step you take not only enhances your reports but also deepens your understanding of the possibilities within Power BI.