Power Query is a data transformation and manipulation tool that’s available in Microsoft Excel and Power BI. It has a wide range of functions that can be used to transform and manipulate data. In this tutorial, you’ll learn how to use the each expression in Power Query / M Language. You can watch the full video of this tutorial at the bottom of this blog.
Power Query Each Expression: Overview
Most of the standard library functions take M parameter functions as arguments. They’re also often unary, which is a function that takes a single argument and performs some form of operation on it.
The each expression is a shorthand for declaring untyped functions taking a single formal parameter, named as the underscore ( _ ).
When you see an each function in an M code, the formula or expression after it will be applied to each item in a list or column.
The underscore parameter can be used to access the current item. However, when used in combination with the lookup operator ( => ) to select fields or columns, the underscore is often omitted.
Examples Of Using The Power Query Each Expression
Let’s look at an example in Power Query. The goal is to filter to only show years greater than 2020.
Without Using The Each Expression
In the Applied Steps pane, you can see that the first step is using a Function Expression.
The function consists of an underscore within a set of parentheses. Then, it’s followed by the lookup operator, which prefixes the variable to the column or field name. This allows us to select rows from the table where the Year values are greater than 2020.
In another example, you can see that because the lookup operator is used to select a field or column, the underscore before the Year field can be omitted. This will still give the same result.
While Using The Each Expression
In this example, you can see that by using the each expression, the nameless variable represented by the underscore can be omitted.
You can also completely emit the underscores in the formula when using lookup to select a field or column. This will continue to give the same result.
Using The Built-in Filter Option In A Power Query Table
If you use the built-in filter in a Power Query table, you’ll see that it generates the same code as the last example.
Revert the table back to show the complete rows. Then, click the drop-down arrow in the Year column and select Number Filters > Greater Than.
In the Filter Rows wizard, write 2020 in the text box and then click OK.
In the formula bar, notice that the M code generated by the filter uses the each expression similar to the last example.
Using the each function makes the M code easier to understand.
When using the Power Query user interface to design your queries, the M engine will, in most cases, use this shorthand M code for you.
***** Related Links *****
What Is Power Query & M Language: A Detailed Overview
Power Query Functions, Keywords, And Identifiers
Power BI Custom Query Functions
Conclusion
The each expression is a useful tool in Power Query for applying functions or expressions to each row or element in a list. It helps simplify M code to make it easier for users to understand. It can also be used with a wide range of functions and operators, allowing you to perform a variety of tasks on your data.
In conclusion, the each expression is a powerful and useful tool that can help you more efficiently and effectively manipulate data in the M language.
All the best,
Melissa de Korte
