Understanding each and _ (Underscore) in Excel Power Query
If you're new to Excel Power Query and curious about how the "each" keyword and "_" (underscore) function, you've come to the right place! In this blog post, we'll explore the concept of "each" and "_" in the context of Lists in Power Query. With the help of a simple example inspired by a YouTube tutorial, you'll gain a clear understanding of how these elements work together. By the end of this guide, you'll be equipped to use these concepts confidently in your Power Query workflows.
Getting Started: Creating a List in Power Query
To begin, open the Power Query Editor in Excel. For our example, we’ll create a list of numbers from 1 to 10. Here’s how to do it:
- Open a Blank Excel Workbook.
- Go to Data in the ribbon and click the icons as follows:
Data→Get Data→From Other Sources→Blank Query - Write the following formula in the formula bar:
= {
1 .. 10
}
This syntax, using curly braces
{} with two dots, between 1 and 10, generates a list of consecutive numbers from 1 to 10.
Transforming the List: Calculating Squares of Each Number
Now, let’s transform this list so that each number is squared.
Our list
To achieve this, we’ll use the
Our list
[1, 2, 3, ..., 10] will be transformed into [1, 4, 9, ..., 100].To achieve this, we’ll use the
List.Transform function, which applies a transformation to each item in a list.Step-by-Step Guide
Step 1: Add a new step by clicking the FX button in the Power Query editor.
This will create a new step and automatically reference the previous one (i.e., the list of numbers we created).
= List.Transform (
You’ll see a tooltip (IntelliSense) that shows the function's syntax:
=
List.Transform
(
list
as
list,
transform
as
function
)
as
list
This tells us that: The first argument should be a list.
The second argument should be a function that transforms each item in the list.
Step 3:For the first argument, use the name of the previous step (which contains the list).
For the second argument, we’ll create a function.
Creating a Function Using a Variable
A function in Power Query can be created using a variable to represent each item in the list.
Here’s how:
Open double parentheses ( ) and define a variable inside them.
Let’s use i as our variable.
(i) => i * i
Here
- i represents each item in the list.
- => is part of the syntax for creating a function, similar to “equals and greater than.”
- i * i instructs Power Query to square the variable i.
Close the List.Transform function and press Enter
List.
Transform
(PreviousStepName,
(
i
)
=>
i
*
i
)
Power Query will now return the squared values of each number in the list:
1 becomes 1 (1 × 1 = 1)
2 becomes 4 (2 × 2 = 4)
3 becomes 9 (3 × 3 = 9)
...
10 becomes 100 (10 × 10 = 100)
Introducing each and _ (Underscore)
Now that we’ve created a function manually, let’s simplify it using each and _. These provide a shorthand, or “sugar syntax,” for creating functions in Power Query.
What is "each" in Power Query?
The keyword each tells Power Query to apply a function to each item in the list, without explicitly defining a variable like i. It simplifies the process of creating functions.
What is "_ "(Underscore) in Power Query?
The _ (underscore) is a placeholder that represents each item in the list. It acts as a default variable in this shorthand syntax.
Using each and _ in Our Example
Instead of writing
((i) => i * i)
We can replace it with:
each _ * _
Here’s the updated formula:
List.Transform(PreviousStepName, each _ * _)
This does the same thing:
each tells Power Query to apply the function to every item in the list.
_ represents each item.
_ * _ instructs Power Query to square each item.
When you press Enter, you’ll get the same result:[1, 4, 9, ..., 100]
Key Takeaways
By following this example, you’ve learned two important concepts in Power Query:
each and _ Simplify Function Creation:
each is shorthand for applying a function to every item in a list.
_ acts as a placeholder for each item.
Power Query’s Functionality is Versatile
Variables (e.g., i) and shorthand (e.g., _) achieve the same results.
Understanding both approaches gives you flexibility when working with lists and tables in Power Query.
Understanding both approaches gives you flexibility when working with lists and tables in Power Query.
Conclusion
The each keyword and _ (underscore) in Power Query are powerful tools that simplify the process of transforming data. Whether you’re working with lists or tables, these concepts make it easier to write efficient and clean code.
Now that you’ve seen a practical example, try it out for yourself!
Create a list, use List.Transform, and experiment with both the explicit function syntax and the shorthand each and _. With practice, you’ll be able to use these tools confidently in your data transformations.
We will see more such examples in our upcoming blogs to understand the concept better and in various scenarios.
EmoticonEmoticon