M Language Basics

M Language (also called Power Query Formula Language) is the scripting language used behind the scenes in Power Query to transform and manipulate data.

Every step you perform in Power Query automatically generates M code.

What is M Language?

• Functional programming language
• Case-sensitive
• Used in Power Query
• Automates data transformation steps
• Works step-by-step

You can view M code by going to:

Power Query Editor → Home → Advanced Editor

Basic Structure of M Code

Most queries follow this structure:

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source, {{"Amount", type number}})
in
    ChangedType

Explanation:

• let → Defines steps
• Each line = One transformation step
• in → Returns final result

Important Concepts in M Language

Steps

Each transformation creates a named step.

Example:

FilteredRows = Table.SelectRows(Source, each [Sales] > 50000)

Case Sensitivity

M is case-sensitive:

Correct:

Table.SelectRows

Incorrect:

table.selectrows

Data Types

Common data types:

• type text
• type number
• type date
• type datetime
• type logical

Example:

Table.TransformColumnTypes(Source, {{"Date", type date}})

Filtering Data

Example:

Table.SelectRows(Source, each [Department] = "IT")

Filters only IT department.

Adding Custom Column

Table.AddColumn(Source, "Profit", each [Sales] - [Cost])

Creates a new column called Profit.

Removing Columns

Table.RemoveColumns(Source, {"UnnecessaryColumn"})

Comments in M

Use double slash:

// This is a comment

Why Learn M Language?

• Customize advanced transformations
• Automate complex logic
• Modify auto-generated steps
• Improve data modeling skills
• Essential for Power BI and advanced Excel users

M Language vs Excel Formulas

M Language	Excel Formula
Works in Power Query	Works in worksheet
Applied before loading data	Applied after data loads
Step-based transformations	Cell-based calculations
Handles large data efficiently	Slower with large datasets

Best Practices

• Keep step names clear
• Avoid unnecessary steps
• Use Advanced Editor carefully
• Test transformations step-by-step
• Learn basic functions first

Conclusion

M Language is the foundation of Power Query. Understanding its basics helps you control and customize data transformations efficiently, making your Excel data preparation more powerful and professional.