Mastering-M-code-a-Beginners-guide-to-understanding-power-query-function-signatures-in-intellisense

 Mastering M Code: A Beginner's Guide to Understanding Power Query Function Signatures with IntelliSense

When you're working with Power Query's M code, you might notice that when you start typing a function, a small pop-up (called IntelliSense) appears, showing you how to use that function. This pop-up shows a "function signature," which is like a quick instruction manual for the function.

List.Transform

List.Transform(list as list, transform as function) as list

What does it mean when you see something like this.

Part 1 of the code:

list as list: This means the first thing you need is a list (a collection of items like numbers or text).

Part 2 of the code:

transform as function: This means the second thing you need is a function (a rule or formula) that tells how to change each item in the list.

Part 3 of the code:

This visually connects the idea that you're taking a list, applying a rule, and getting a new list as a result.

Mastering-power-query-m-code-a-beginners-guide-to-Learning-Date-Transformations

Mastering Date Transformations in Power Query: A Beginner’s Guide to M Code

Transforming and analyzing date-related data is an essential skill when working with Power Query in Excel. Whether you're creating custom columns to extract the name of the month, the day of the week, or performing other date-related transformations, mastering M Code can take your data analysis to the next level. But don’t worry if you’re new to Power Query — we’ll simplify everything for you!

In this blog post, we’ll dive deep into the world of date transformations using Power Query. Starting with the basics, we’ll walk you through how to extract specific details, such as abbreviations for months and days, and explore other useful transformations to make your data more meaningful. With easy-to-understand explanations, step-by-step M Code examples, and practical use cases, this guide will help you unlock the full potential of date data in Power Query.

By the end of this blog, you'll not only understand how to manipulate date columns effortlessly but also gain confidence in writing M Code to achieve customized transformations. Let’s get started and make date transformations in Power Query as easy as a few clicks!

1. Extract the Year from the Date

This transformation creates a new column that extracts the year from the [Date] column.

M Code:

= Table.AddColumn(#"PreviousStepName", "Year", each Date.Year([Date]), type number)


Output Example:

Date	Year
2023-05-12	2023
2023-06-15	2023
2024-01-01	2024

---

2. Extract the Month Number

This transformation creates a new column that extracts the numeric value of the month (e.g., 1 for January, 2 for February).

M Code:

= Table.AddColumn(#"PreviousStepName", "Month_Number", each Date.Month([Date]), type number)

Output Example:

Date	Month_Number
2023-05-12	5
2023-06-15	6
2024-01-01	1

---

3. Extract the Week Number

This transformation creates a column to show the week number of the year for each date.

M Code:

= Table.AddColumn(#"PreviousStepName", "Week_Number", each Date.WeekOfYear([Date]), type number)

Output Example:

Date	Week_Number
2023-05-12	19
2023-06-15	24
2024-01-01	1

---

4. Extract the Day Number

This transformation creates a new column showing the day number of the month for each date.

M Code:

= Table.AddColumn(#"PreviousStepName", "Day_Number", each Date.Day([Date]), type number)

Output Example:

Date	Day_Number
2023-05-12	12
2023-06-15	15
2024-01-01	1

---

5. Is the Date a Weekend?

This transformation creates a column to indicate whether the date falls on a weekend (Saturday or Sunday).

M Code:

= Table.AddColumn(#"PreviousStepName", "Is_Weekend", each Date.DayOfWeek([Date], Day.Sunday) >= 5, type logical)

Output Example:

Date	Is_Weekend
2023-05-12	false
2023-05-13	true
2023-05-14	true

---

6. Find the Quarter of the Year

This transformation creates a column showing the quarter of the year (e.g., Q1, Q2, Q3, Q4).

M Code:

= Table.AddColumn(#"PreviousStepName", "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date])), type text)

Output Example:

Date	Quarter
2023-05-12	Q2
2023-06-15	Q2
2024-01-01	Q1

---

7. Day of the Year

This transformation calculates the day number of the year for each date (e.g., January 1 is day 1, December 31 is day 365 or 366).

M Code:

= Table.AddColumn(#"PreviousStepName", "Day_Of_Year", each Date.DayOfYear([Date]), type number)

Output Example:

Date	Day_Of_Year
2023-05-12	132
2023-06-15	166
2024-01-01	1

---

8. Add Custom Date Formats

This transformation creates a column that formats the date in a specific way (e.g., MM/dd/yyyy).

M Code:

= Table.AddColumn(#"PreviousStepName", "Custom_Date_Format", each Text.PadStart(Text.From(Date.Month([Date])), 2, "0") & "/" & Text.PadStart(Text.From(Date.Day([Date])), 2, "0") & "/" & Text.From(Date.Year([Date])), type text)

Output Example:

Date	Custom_Date_Format
2023-05-12	05/12/2023
2023-06-15	06/15/2023
2024-01-01	01/01/2024

---

9. First Day of the Month

This transformation calculates the first day of the month for each date.

M Code:

= Table.AddColumn(#"PreviousStepName", "First_Day_Of_Month", each Date.StartOfMonth([Date]), type date)

Output Example:

Date	First_Day_Of_Month
2023-05-12	2023-05-01
2023-06-15	2023-06-01
2024-01-01	2024-01-01

---

10. Last Day of the Month

This transformation calculates the last day of the month for each date.

M Code:

= Table.AddColumn(#"PreviousStepName", "Last_Day_Of_Month", each Date.EndOfMonth([Date]), type date)

Output Example:

Date	Last_Day_Of_Month
2023-05-12	2023-05-31
2023-06-15	2023-06-30
2024-01-01	2024-01-31

---

11. Weekday Number (0 = Sunday, 6 = Saturday)

This transformation creates a column showing the day of the week as a number (0 for Sunday, 1 for Monday, etc.).

M Code:

= Table.AddColumn(#"PreviousStepName", "Weekday_Number", each Date.DayOfWeek([Date], Day.Sunday), type number)

Output Example:

Date	Weekday_Number
2023-05-12	5
2023-05-13	6
2023-05-14	0

---

12. Custom Weekday Labels

This transformation allows you to create a custom weekday label for each date.

M Code:

= Table.AddColumn(#"PreviousStepName", "Custom_Weekday_Label", each if Date.DayOfWeek([Date], Day.Sunday) = 0 then "Weekend" else "Weekday", type text)

Output Example:

Date	Custom_Weekday_Label
2023-05-12	Weekday
2023-05-13	Weekend
2023-05-14	Weekend

---

Some Advanced M Code functions related to Date Transformations:

To extract the Name of the Month (e.g., Jan from the date 2023-01-15) from the date in the given example, you can use the Date.MonthName function in Power Query (M code). Below is the M code to accomplish this:

Date	Month
2023-01-15	Jan
2023-02-20	Feb
2023-03-10	Mar

M Code:

= Table.AddColumn(#"Added Custom", "Month", each Text.Start(Date.MonthName([Date]),3), type text)



---

Detailed Parsing:

1 ) = Table.AddColumn(

• Meaning: This is a function in Power Query (M language) that adds a new column to an existing table.
• Action: It specifies that you are adding a new column to the table from the previous step.

2) #"Added Custom"

• Meaning: This is the name of the table from the previous step in your query. In Power Query, steps are often referenced by their names.
• Action: The function will add the new column to this table. This is a reference to an existing table.

3) "Month",

• Meaning: This is the name of the new column that will be added to the table.
• Action: A new column named "Month" will be created.

4) each

• Meaning: This is a keyword in Power Query that is used to apply a function to each row of the table.
• Action: It signals that the operation defined next should be applied to each row in the table.

5) Text.Start(

• Meaning: This is a function that returns a specified number of characters from the start of a text string.
• Action: The function will extract the first few characters from the result of Date.MonthName.

6) Date.MonthName([Date])

• Meaning: The Date.MonthName function returns the full name of the month (e.g., "January", "February") for the value in the [Date] column.
• Action: For the given row's [Date] value, this extracts the name of the month.

7) [Date]

• Meaning: This is a reference to the column named "Date" in the current row.
• Action: The function takes the value in the [Date] column of the current row and uses it as input for Date.MonthName.

8) 3),

• Meaning: This specifies the number of characters to extract from the text returned by Date.MonthName. For example, "January" becomes "Jan".
• Action: Only the first 3 characters of the month name are extracted.

9)  type text

• Meaning: This specifies the data type of the new column being added. In this case, the column will contain text values.
• Action: Ensures that the new column "Month" is of the text data type.

---

Full Explanation in Plain English:
Step 1: The function starts by referencing the table from the previous step (called #"Added Custom").
Step 2: A new column named "Month" will be added to this table.
Step 3: For each row in the table:

• The Date.MonthName([Date]) function retrieves the full name of the month (e.g., "January", "February") from the value in the [Date] column.
• The Text.Start function then extracts the first 3 characters of the month name (e.g., "Jan" from "January").

Step 4: The new column is defined as text using type text.

---

Improved and Commented M Code:

Here’s the same M code with inline comments (in Blue) for clarity:

= Table.AddColumn(#"Changed Type",/* Reference to the previous step/table in the query */

"Custom", /* Name of the new column to be added */

each Text.Start( /* Apply the following function to each row */

Date.MonthName([Date]), /* Get the full month name from the "Date" column */

3 /* Extract only the first 3 characters (e.g., "Jan" from "January") */

), type text)

---

M Code Made Easier for a Layman:

Here’s how this code works step by step in simple terms:

1. Imagine you have a table with dates in one column (e.g., "2023-01-15").
2. You want to add a new column to this table that shows the first three letters of the month (e.g., "Jan").
3. The M Code:= Table.AddColumn(#"Added Custom", "Month", each Text.Start(Date.MonthName([Date]),3), type text):

• Looks at each row in the table.
• Takes the date from the [Date] column.
• Converts the date into the full month name (e.g., "January").
• Extracts the first three letters (e.g., "Jan").
• Adds this result as a new column called "Month".
• Finally, it ensures that the new column contains text values.

---

What You’ll See in Power Query:

After applying this code, the table will look something like this:

Date	Month
2023-01-15	Jan
2023-02-20	Feb
2023-03-10	Mar

---

To extract the day number (e.g., 15 from 2023-01-15) from the date in the previous example, you can use the Date.Day function in Power Query (M code). Below is the M code to accomplish this:

M Code:

= Table.AddColumn( #"Added Custom", // Reference to the previous step/table in the query "Day", // Name of the new column to be added each Date.Day([Date]), // Extract the day number from the "Date" column Int64.Type // Specify the data type as an integer )

Explanation:

1. Table.AddColumn:

   • This function adds a new column to the table.

2. #"Added Custom":

   • This is the name of the table from the previous step in the query.

3. "Day":

   • The name of the new column that will contain the extracted day numbers.

4. each Date.Day([Date]):

   • The Date.Day function retrieves the day number (e.g., 15 from 2023-01-15) from the [Date] column for each row.

5. Int64.Type:

   • This sets the data type of the new column to a 64-bit integer, which is suitable for day numbers.

Result:

After applying this code, your table will look like this:

Date	Day
2023-01-15	15
2023-02-20	20
2023-03-10	10

---

For a Layman:

This code will:

• Add a new column to your table.
• Look at the [Date] column for each row.
• Extract the day number from the date (e.g., 15 for January 15, 2023).
• Add this number as a new column called "Day".

Let's break down the provided M Code step by step, add commentary to guide you, and provide an easy-to-understand explanation for what it does:

---

M Code:

= Table.AddColumn(#"Added Custom", "Day_Of_Week", each Text.Start(Date.DayOfWeekName([Date]),3), type text)

---

Step-by-Step Parsing and Commentary:

1. = Table.AddColumn:

   • This is a function in Power Query used to add a new column to an existing table.
   • The new column will be calculated based on the logic provided in the code.

2. #"Added Custom":

   • This is a reference to the previous step in your query. In Power Query, every step is named (either automatically or manually by you).
   • In this case, "Added Custom" refers to the name of the table/output from the previous step.

3. "Day_Of_Week":

   • This is the name of the new column being created.
   • The new column will store the abbreviated names of the days of the week (e.g., "Mon", "Tue", "Wed").

4. each:

   • This is a keyword in Power Query that is used to apply the subsequent logic to each row in the table.
   • It essentially means "perform the following operation on every row in the table."

5. Date.DayOfWeekName([Date]):

   • This is a Power Query function that retrieves the full name of the day of the week for the value in the [Date] column.
   • For example:
     • If [Date] is 2023-05-12, this function will return "Friday".
     • If [Date] is 2023-05-13, this function will return "Saturday".

6. Text.Start(..., 3):

   • The Text.Start function takes the result of Date.DayOfWeekName (e.g., "Friday") and extracts the first 3 characters.
   • For example:
     • "Friday" becomes "Fri".
     • "Saturday" becomes "Sat".
   • This is how you get the abbreviated day names in the new column.

7. type text:

   • This specifies that the data type of the new column should be text.
   • This ensures that the values in the "Day_Of_Week" column are stored as text (e.g., "Mon", "Tue").

---

What Does This Code Do?

• The code creates a new column called "Day_Of_Week".
• For each row in the table:
  1. It looks at the value in the [Date] column.
  2. It calculates the full day name (e.g., "Monday" or "Tuesday") using the Date.DayOfWeekName function.
  3. It extracts the first 3 characters of the day name (e.g., "Mon", "Tue") using the Text.Start function.
  4. The result is stored in the new column as text.

---

Example:

If your table initially looks like this:

Date
2023-05-12
2023-05-13
2023-05-14

After applying the M Code, it will look like this:

Date	Day_Of_Week
2023-05-12	Fri
2023-05-13	Sat
2023-05-14	Sun

---

For Beginners:

This code will:

1. Create a new column called "Day_Of_Week".
2. For each row, look at the date in the [Date] column.
3. Figure out the day of the week (e.g., "Monday" or "Friday") for the date.
4. Take the first 3 letters of the day (e.g., "Mon" or "Fri").
5. Save this value as text in the new column.

---

Notes:

• Make sure the [Date] column in your table contains valid date values.
• If the [Date] column contains invalid data (e.g., text or null values), you may encounter errors.

Mastering-power-query-m-code-a-beginners-guide-to-learning-and-transforming-data-in-excel-and-power-bi

Mastering Power Query M Code: A Beginner's Guide to Simplify Data Transformation

Are you finding it challenging to manipulate and transform data in Power Query using M code? If so, you're not alone. M language, the backbone of Power Query in Excel and Power BI, can seem intimidating at first, but with the right strategy and resources, you can master it and unlock its true potential. This guide provides a structured approach to learning M code, complete with practical examples and tips to help you build confidence and fluency.

---

1. Start with the Basics: Understand Power Query

Before diving deep into M code, get familiar with the fundamentals of Power Query:

• Use the Power Query interface in Excel or Power BI to learn the basics of loading, transforming, and combining data.
• Perform actions like filtering rows, sorting data, and renaming columns. These generate M code automatically in the background, which you can study in the Advanced Editor.

Key Tip: Start small and gradually explore more complex operations as you gain confidence.

---

2. Break Down and Analyze Queries

Each query in Power Query generates M code. Use this as a learning opportunity:

1. Load a dataset into Power Query.
2. Perform basic operations like removing duplicates or renaming columns.
3. Open the Advanced Editor to view the M code generated for these steps.
4. Experiment with modifying the code in the editor to understand how it affects your query.

By doing this, you'll start to recognize patterns and common functions used in M code.

---

3. Master Key Concepts of M Language

a. Understand the Data Structures

M code revolves around three key structures:

• Tables: Used for structured, tabular data.
• Records: Represent single rows or objects.
• Lists: Single-column collections of data (like arrays).

Learn the functions used to manipulate these structures, such as:

• Table.AddColumn
• Table.SelectRows
• Table.Group
• List.Combine
• List.Sort
• List.Transform
• Record.FieldValues
• Record.ToTable

b. Learn the Function Syntax

Most M functions follow this structure:

FunctionName(parameter1 as type, parameter2 as type, ...) as returnType

For example:

Table.SelectRows(table as table, condition as function) as table

c. Explore Commonly Used Functions

Get comfortable with frequently used functions in M:

• Text Functions: Text.Upper, Text.Length, Text.Split
• Number Functions: Number.Round, Number.Mod
• Table Functions: Table.TransformColumns, Table.RemoveColumns
• List Functions: List.Sum, List.Distinct, List.Accumulate

---

4. Practice with Real-World Scenarios

Applying M code to real-world data challenges is the best way to build your skills. Here are a few examples:

Scenario 1: Cleaning Data

• Remove extra spaces, fix text case inconsistencies, or extract specific data from messy columns.

Scenario 2: Combining Data

• Append or merge multiple tables from different sources, such as Excel files or CSVs.

Scenario 3: Dynamic Calculations

• Add calculated columns to tables for custom metrics or aggregated data.

Scenario 4: Reshaping Data

• Use pivot and unpivot operations to transform data into the desired structure.

---

5. Use Resources to Deepen Your Knowledge

a. Official Documentation

The Power Query M Language Reference by Microsoft is the go-to resource for M code syntax and functions.

b. Video Tutorials

Search for Power Query tutorials on YouTube. Popular creators like Guy in a Cube, Chandoo, and ExcelIsFun often explain M code concepts clearly.

c. Books

• M is for (Data) Monkey by Ken Puls and Miguel Escobar
• Power Query for Power BI and Excel by Christopher Webb

d. Forums and Communities

• Microsoft Power Query Forum
• Power BI Community

---

6. Experiment with Custom Functions

Once you've grasped the basics, create your own custom functions for reusable operations.

For example, a function to calculate discounts:

let CalculateDiscount = (Price as number, DiscountRate as number) => let DiscountAmount = Price * DiscountRate, FinalPrice = Price - DiscountAmount in FinalPrice in CalculateDiscount

---

7. Build a Cheat Sheet

Create a personalized cheat sheet to quickly reference M code syntax and common functions. This will save time and make learning more structured.

---

8. Debug and Experiment

The best way to learn M code is by trial and error:

• Use Power Query to create queries, then debug and modify them in the Advanced Editor.
• Explore the #shared function to discover built-in M functions and their descriptions.

---

9. Share Your Knowledge

One of the best ways to solidify your understanding is by teaching others.
Consider writing blogs, recording tutorials, or sharing tips on forums.

---

10. Stay Consistent

Learning M code takes time, so practice consistently. Dedicate 30 minutes daily to working on small challenges or exploring new functions.

---

Conclusion

Mastering Power Query M code can transform the way you handle data. By understanding the fundamentals, practicing real-world scenarios, and leveraging available resources, you’ll be able to clean, transform, and analyze data efficiently. Whether you're a data analyst, business professional, or Excel enthusiast, these skills will give you a significant edge in your field.

---

This guide was created with the help of ChatGPT to provide you with a comprehensive learning path. Feel free to share it with others who might benefit! 😊

Understanding Each and Underscore in the context of List in Microsoft Excel Power Query

 

Understanding each and _ (Underscore) in Excel Power Query

If you're new to Excel Power Query and curious about 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:

1. Open a Blank Excel Workbook.
   
   
2. Go to Data in the ribbon and click the icons as follows:
   
   
   Data

   →

   Get Data

   →

   From Other Sources

   →

   Blank Query
   


3. 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 [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).

Step 2: In the formula bar, type the following:

= 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.

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.

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!