M Language Basics
Power BI · Power Query (Data Transformation)
Theory
M (the Power Query Formula Language) is the functional language behind every Power Query step. A query is a let expression: each line inside let defines a named step, usually by calling a function on the previous step, and the in clause names the step whose result is returned. Step names with spaces are written as #"Step Name".
Functions follow Category.Action naming, such as Table.SelectRows, Table.AddColumn or Text.Upper. The keyword each creates a small function applied per row, and [Amount] inside it means the Amount field of the current row. So Table.SelectRows(#"Changed Type", each [Amount] > 1000) keeps only orders above ₹1,000. M is case-sensitive: Table.selectrows will fail. You rarely write M from scratch, but reading and small edits in the Advanced Editor are very useful.
Why we use it
The ribbon covers most needs, but knowing M lets you fix broken steps, rename references, use parameters and write logic the UI does not offer. It also makes troubleshooting refresh errors much easier.
When to use it
Use M edits for small tweaks: changing a filter value, adding a custom column, referencing a parameter. Keep business calculations that must respond to slicers in DAX, not M, because M runs only at refresh time.
How it works
- Query starts at the Source step
- Each step calls a function on the previous step
- each [Amount] > 1000 is tested on every row
- Condition true for the row? Yes: Keep the row. No: Drop the row.
- The in clause returns the final step
- Result loads into the model
Syntax
let
Step1 = <expression>,
#"Step Two" = Function(Step1, ...)
in
#"Step Two"
Table.SelectRows(table as table, condition as function) as table
each [Column] > value // row function shorthandExample
let
Source = Excel.Workbook(File.Contents("C:\Data\Sales.xlsx"), null, true),
Sales_Table = Source{[Item = "Sales", Kind = "Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sales_Table,
{{"OrderID", Int64.Type}, {"Region", type text}, {"Amount", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Amount] > 1000)
in
#"Filtered Rows"Key points
- A query is let ... in; each step references the previous one
- Step names with spaces use #"..." syntax
- each [Col] is shorthand for a function over the current row
- M is case-sensitive: Table.SelectRows, not table.selectrows
- M runs at refresh time; DAX runs when users interact