What Is Power Query M Language?
Power Query M is the formula language behind every transformation in Power Query — both in Power BI and Excel. Each step you create through the Power Query Editor UI generates an M expression behind the scenes. Learning M lets you write custom transformations, build reusable functions, and handle scenarios the UI cannot.
Power Query M Formula Language — Key Characteristics
- Case-sensitive: RowCount is valid; table.rowcount is not.
- Functional: M is a functional language — transformations are chained as function calls, each producing a new value without mutating the original.
- Let…in structure: Every query is a ‘let’ block defining named steps, ending with an ‘in’ clause that returns the final result.
- Rich type system: Supports tables, records, lists, numbers, text, dates, durations, and more — each with dedicated library functions.
Power Query M Language Examples
Below are common M patterns:
- Filter rows: SelectRows(Source, each [Amount] > 100)
- Add a custom column: AddColumn(Source, “Profit”, each [Revenue] – [Cost], type number)
- Group and aggregate: Group(Source, {“Region”}, {{“TotalSales”, each List.Sum([Sales]), type number}})
- Replace values: ReplaceValue(Source, null, 0, Replacer.ReplaceValue, {“Amount”})
- Custom function: (inputTable as table) => Table.SelectRows(inputTable, each [Status] = “Active”)
How to Access and Edit M Code in Power BI
In Power Query Editor, click View → Advanced Editor to see the full M code for the selected query. You can also view individual step formulas in the formula bar above the data preview. Editing M directly is useful for debugging, optimizing, or writing transformations that the UI doesn’t support (e.g., custom functions, error handling with try…otherwise, or dynamic query parameters).
How to Write Power Query M Code — Step-by-Step Tutorial
This tutorial introduces you to writing and editing M code directly in the Advanced Editor. You will create a simple custom transformation in about 5 steps and under 10 minutes.
Steps
Step 1: Open the Advanced Eitor
In Power Query Editor, click View → Advanced Editor. You will see the full M code for the current query, structured as a let…in block.
Step 2: Understand the structure
Each line in the ‘let’ block is a named step (e.g., Source, #”Filtered Rows”). The ‘in’ clause at the bottom names the final step whose result is returned. Steps reference earlier steps by name.
Step 3: Add a custom step
After the last step in the ‘let’ block, add a new line: CustomStep = Table.AddColumn(PreviousStepName, “Profit”, each [Revenue] – [Cost], type number), replacing PreviousStepName with the actual name of the step above. Update the ‘in’ clause to return CustomStep.
Step 4: Test and debug
Click Done to apply. If there is a syntax error, Power Query highlights the line. Common issues: missing commas between steps, mismatched parentheses, or referencing a step name that does not exist.
Step 5: Use try…otherwise for error handling
Wrap expressions that might fail: try [Column] / [Divisor] otherwise null. This prevents a single bad row from breaking the entire query.
