Understanding Each and Underscore in Excel Power Query - Beginners Guide

Excel Power Query is a powerful tool for data transformation, and while it simplifies many tasks, some of its syntax can be confusing for beginners. Two commonly used keywords in Power Query are "each" and underscore (_). If you've ever wondered what they mean and how they work, this guide is for you!

In this blog post, we’ll cover:

• What "each" and underscore (_) are.
• Why and when to use them in Power Query.
• Practical examples to help you get started.

Let’s dive in!

---

What Is "Each" in Power Query?

In Power Query, "each" is a shorthand way of creating a function. It is used to define a simple, single-parameter function for transformations. Think of it as a way to say: "Apply this operation to each row or column in my data."

Example 1: Add 10 to Each Value in a Column

Excel  Example - Input and Out put Table using "each"

Let’s say you have a Table with a column named "Sales", and you want to add 10 to every value in that column.

1. With the Table selected in Excel, go to Data in the ribbon and click the icons as follows:
   
   
   Data

   →

   Get Data

   →

   From Other Sources

   →
   
   
   →

   From Table / Range
   
   
2. You will find the Data has been Loaded into Power Query.
3. Go to the Add Column tab and choose Custom Column.
4. Rename Custom to "Sales_Plus_Ten".
5. Enter the following formula in the custom column input box:
   
   "each [Sales] + 10"

Here’s what happens:

M-Code with Usage of "each" in Power Query Editor

Syntax used in Power Query

= Table.AddColumn(#"Changed Type", 

"Sales_Plus_Ten", 

each [Sales]+10)

           
Syntax with comments explaining the sections

= Table.AddColumn /* This is the Add Column Command */

(#"Changed Type", /* This is the Changed Type*/

"Sales_Plus_Ten", /* This is the name of the new added Column */

each [Sales]+10) /* This is How each is used to add 10 to column "Sales"*/

• "each" tells Power Query to apply the operation ([Sales] + 10) to every row in the column.
• The resulting column will contain the original value of Sales plus 10 for each row.
• To Load the result back to Excel, select the commands in Power query as follows:
  
  
  Home

  →

  Close and Load

  →

  Close and Load to

  →
  
  
  →

  Existing Worksheet

  →

  Enter the location cell

  →

  Click "OK"

When to Use "Each"

• When performing simple operations on a column or table, like adding, subtracting, filtering, or modifying values.
• To create custom formulas without explicitly defining a function name.

---

What Is Underscore (_) in Power Query?

The underscore (_) is used as a placeholder for the current record or row being processed. It represents the current item in the context of a function. If "each" is a shorthand for creating a function, the underscore (_) is the function's input parameter.

Example 2: Multiply Each Value by 2 Using _

Imagine you have a column named "Quantity", and you want to double every value in that column.

Excel  Example - Input and Out put Table using "_"

1. With the Table selected in Excel, go to Data in the ribbon and click the icons as follows:
   
   
   Data

   →

   Get Data

   →

   From Other Sources

   →
   
   
   →

   From Table / Range
   
   
2. Data has been Loaded into Power Query.

Now we are in Power Query Editor. From the ribbon:

Click Add Column and then Custom Column
Give a name "Double".
In the custom Column formula Window enter the following and click OK.

= _ [Quantity] * 2

Here’s what happens:

M-Code with Usage of "_" in Power Query Editor

• The underscore (_) represents the current row or value in the column.
• Power Query multiplies the value in the "Quantity" column by 2 for each row.

---

How "Each" and Underscore (_) Work Together

Both "each" and _ are often used together to simplify operations in Power Query. However, they are not interchangeable; "each" defines the function, while _ represents the input parameter.

Example 3: Filter Rows Based on a Condition

Here’s an example scenario in Power Query using each and _ to filter out items where the price is greater than 50:

Scenario: Filter Rows Where Price > 50 Using each and _

Initial Data Table:

Product	Price
Product A	30
Product B	45
Product C	60
Product D	80
Product E	25
Product F	55

---

Steps:

1. Load the data into Power Query.
2. Go to the Home tab and open the Advanced Editor.
3. Replace or modify the query to include a Table.SelectRows function with the each and _ syntax.

Power Query M Code Example:

m
let
    Source = Table.FromRows(
        {
            {"Product A", 30},
            {"Product B", 45},
            {"Product C", 60},
            {"Product D", 80},
            {"Product E", 25},
            {"Product F", 55}
        },
        {"Product", "Price"}
    ),
    FilteredRows = Table.SelectRows(Source, each _[Price] > 50)
in
    FilteredRows

---

Explanation:

1. Source: Creates a sample table with two columns, "Product" and "Price."
2. Table.SelectRows: Filters rows based on the condition.
   • each _[Price] > 50:
     • each is shorthand for a lambda function.
     • _ represents the current row.
     • _[Price] accesses the "Price" column of the current row.

---

Filtered Output Table:

Product	Price
Product C	60
Product D	80
Product F	55

This method uses each and _ to define a concise filter condition for rows where the "Price" column is greater than 50.

---

Key Differences Between "Each" and _

Feature	"Each"	Underscore (_)
Purpose	Shorthand for creating a function	Represents the current row or value
Usage	Defines an operation for every row	Refers to the data being processed
Example	each [Sales] + 10	_ * 2

---

Advanced Usage of "Each" and _

Here are some more scenarios where "each" and _ come in handy:

Example 4: Combine Two Columns

You have two columns, "First Name" and "Last Name", and you want to create a new column called "Full Name" by combining the two.

1. Go to Add Column > Custom Column.
2. Enter this formula:
   
   each [First Name] & " " & [Last Name]

The result will be a column where each row contains the full name (e.g., "John Smith").

---

Example 5: Transform Text to Uppercase

You have a column called "City", and you want to convert all values to uppercase.

1. Select the "City" column.
2. Go to Transform > Format > Uppercase.
3. Alternatively, use a custom formula:
   
   each Text.Upper([City])

This formula applies the Text.Upper function to every row in the "City" column.

---

Example 6: Create Conditional Columns

You want to create a column that shows "High" if the value in the "Score" column is above 80, and "Low" otherwise.

1. Go to Add Column > Conditional Column.
2. Alternatively, use this formula:
   
   each if [Score] > 80 then "High" else "Low"

---

Conclusion

Understanding "each" and underscore (_) is essential for mastering Power Query. These keywords allow you to create custom transformations, perform calculations, and filter data with ease. By using the examples above, you can start applying these concepts to your own data and make Power Query work for you.

If you're just getting started, experiment with simple transformations and gradually explore more complex scenarios. Don’t forget to use screenshots to document your progress or share your results!

---

Pro Tip: Save your Power Query steps frequently and preview the results to ensure your transformations are working as expected.

Let us know in the comments if you have any questions or additional tips for using "each" and _ in Power Query. Happy learning!