Meaning of each keyword & underscore _ variable in Power Query. A sweet explanation.

Summary

This tutorial explores syntactic sugar in the M language used in Power Query, focusing on the `each` keyword and underscores in function definitions. It illustrates how to filter customer data by country—specifically selecting customers based in Denmark—using the `Table.SelectRows` function. The video demonstrates the evolution of M code from a verbose format to a more concise version, highlighting the importance of readability while noting limitations related to lists and unary functions. Additional resources for further learning are also provided.

Takeaways

• 😀 The Advanced Editor in Power Query allows users to view and edit M code associated with transformation steps.
• 📊 Syntactic sugar in M language simplifies function writing and improves readability.
• 🌍 The tutorial uses a table of customers to demonstrate selecting those based in Denmark.
• 🔍 The `Table.SelectRows` function takes a table input and a filtering function as parameters.
• ✏️ The filtering function can be written in both long form and shorthand, showcasing flexibility.
• 🔄 Using the underscore `_` allows for concise representation of the current row in filtering functions.
• 📝 The `each` keyword serves as a more readable alternative to the underscore, streamlining the code.
• ⚠️ The underscore can only be removed if referencing fields or columns; otherwise, it remains necessary.
• 🔧 Syntactic sugar is primarily suited for unary functions that take a single input.
• 📁 Additional resources, including a purchasable Power BI file, are available for further learning.

Q & A

What is the purpose of the M language in Power Query?

The M language is used in Power Query to define and perform data transformation steps.

What is syntactic sugar in the context of M language?

Syntactic sugar refers to a syntax that makes code easier to read and write, allowing for simpler expressions without changing the functionality.

How do you filter customers from Denmark in M language?

You can use the 'Table.SelectRows' function to filter the customers by specifying a condition that the country equals 'Denmark'.

What are the parameters of the 'Table.SelectRows' function?

The 'Table.SelectRows' function takes two parameters: the table to filter and a function that defines the condition for filtering.

What is the significance of the 'each' keyword in M language?

The 'each' keyword is syntactic sugar that simplifies writing functions by automatically iterating over each row in a table.

Can you rename the input variable when defining a function in M language?

Yes, you can name the input variable anything you want; however, using meaningful names like 'row' can enhance readability.

What happens if you remove the underscore variable in certain contexts?

You can remove the underscore if it references a row context with fields or columns, but if transforming a list, the underscore must be retained.

What types of functions is syntactic sugar mainly used for?

Syntactic sugar is primarily designed for unary functions, which are functions that take a single input.

What limitations exist when using the 'each' keyword and underscore?

The main limitations are that the underscore must be retained when working with lists, and syntactic sugar is intended for unary functions.

Where can additional examples of M language syntax be found?

Additional examples can be found in a Power BI file created to accompany the tutorial, which can be purchased from their store.