M is for data monkey is the approachable nickname for the M language that lives inside Excel Power Query. Learning this formula language lets you describe exactly the shape of your data transformations instead of clicking through wizards step by step.
This guide walks through the core ideas, practical patterns, and everyday questions so you can use M confidently when you shape, filter, and blend data in Power Query.
| Concept | M Syntax Example | Purpose | Power Query UI Equivalent |
|---|---|---|---|
| Table reference | Source => Excel.CurrentWorkbook(){[Name="Sales"]}[Content] | Load a named table into Power Query | td>From Table/Range in Get Data|
| Column selection | Table.SelectColumns(Source, {"Date", "Revenue"}) | Keep only the columns you need | Choose Columns in Power Query |
| Row filtering | Table.SelectRows(Source, each [Revenue] > 1000) | Include rows that meet a condition | Filter Rows in Power Query |
| Column derivation | Table.AddColumn(Source, "Tax", each [Revenue] * 0.2) | Create new columns with expressions | Add Custom Column in Power Query |
| Function creation | DiscountRate = (rate as number) => 1 - rate | Reuse logic across steps | Parameterized steps or custom functions |
Getting Started with M in Power Query
The M language is the underlying expression language of Power Query, which is available in Excel, Power BI, and other Microsoft products. Rather than recording clicks, you write or edit M to control every transformation with precision.
When you use the Power Query Editor UI, each step appears as a line in the Applied Steps pane. Under the hood, those steps are written in M, which means you can peek at the script and even edit it directly to fine tune behavior.
Core M Building Blocks for Excel Users
At the heart of M are tables, lists, records, and functions. A table is a set of rows and columns, a list is an ordered set of values, and a record is a related group of named values, similar to a single row without the column structure.
Learning a few function patterns, such as Table.SelectRows and Table.AddColumn, lets you handle the majority of cleaning tasks. Because M is case sensitive and uses square brackets for column names, exact matching of names and consistent quoting helps avoid frustrating errors.
Expression Structure and Readability
Each M statement returns a new value, often a table that represents your current state of preparation. Chaining steps with the #"..." naming style keeps your code organized even as the step count grows.
You can split complex logic into smaller, named steps for readability and reuse. This modular approach makes troubleshooting easier and prevents long single formulas that are hard to maintain in Excel.
Writing and Editing M Code Inside Power Query
Inside the Power Query Editor, you open the Advanced Editor to see and edit the full M script for your query. Pasting well formed snippets, renaming steps, and adjusting parameter values lets you push beyond what the UI alone can do.
Using descriptive step names and indentation helps you and teammates understand the flow quickly. Because Power Query captures dependencies automatically, rearranging steps often requires only small script updates instead of a full rewrite.
Handling Common Data Tasks with M
You can reshape data, pivot columns, unpivot other columns, merge queries, and append multiple files without leaving Excel. M makes it straightforward to standardize date formats, clean messy text, and apply consistent rounding or currency conversions across many tables.
Combining conditional logic, error handling with try otherwise, and list functions allows you to build robust pipelines that keep working when source files gain new rows or slightly different structures.
Working Efficiently with M for Excel Data Prep
Understanding M gives you precise control and makes your Excel data prep more reliable and repeatable.
- Use the Applied Steps pane to keep your transformation flow clear and ordered.
- Name key steps descriptively so scripts are easier to read and debug.
- Start with UI actions and refine critical parts with custom M when needed.
- Test each step independently to isolate issues quickly.
- Leverage custom functions to avoid duplicating logic across queries.
- Document complex expressions with brief inline comments when necessary.
FAQ
Reader questions
Can I mix UI steps and handwritten M in the same query?
Yes, you can add steps through the UI and then switch to Advanced Editor to insert or adjust M code. As long as the syntax is valid and produces a table, Power Query merges these approaches seamlessly.
What happens if a column name changes in the source data?
M references column names literally, so a renamed column will cause an error. You can handle this by using tolerant references, renaming columns early in the process, or creating fallback logic with try otherwise.
How do I reuse the same transformation across multiple queries?
Define a custom function in an empty query using the Advanced Editor, then call that function from other queries. This keeps logic consistent and makes global updates much faster.
Is M case sensitive and sensitive to extra spaces?
Yes, M is case sensitive and treats column names and syntax exactly as written. Extra spaces around column references or literals can cause errors, so careful naming and consistent quoting are important.