learn.fttgsolutions.com · Data & BI
Quick reference for Power Query M — the functional language behind Power BI and Excel data transformations.
FTTG · May 19, 2026 · 12 min read
Quick reference for Power Query M — the functional language behind Power BI and Excel data transformations. Covers values, operators, text, numbers, dates, lists, records, and tables.
📋 Quick reference: Power Query M Cheat Sheet — use this alongside the guide for fast syntax lookup while you read.
Power Query is the data transformation engine built into Power BI, Excel, and Microsoft Fabric. It lets you connect to data sources, clean and reshape data, and load it into a model — without writing SQL or Python. Every transformation you apply in the Power Query Editor generates M code behind the scenes.
M (officially "Power Query Formula Language") is the functional language that powers every transformation in Power Query. It is case-sensitive, expression-based, and lazy — steps are only evaluated when needed. You write M directly in the Advanced Editor or it is generated automatically by the UI.
Everything in M is an expression that evaluates to a value. A value can be a number, text, a list, a record, or a table. There are no statements or loops in the traditional sense — just expressions that produce values.
// A simple expression
1 + 1 // evaluates to 2
// A text expression
"Hello" & " " & "World" // evaluates to "Hello World"
A query is a let...in expression. let defines named steps. in specifies which step is the final output.
let
Source = Csv.Document(File.Contents("C:\data\sales.csv")),
Promoted = Table.PromoteHeaders(Source),
TypedCols = Table.TransformColumnTypes(Promoted, {
{"Date", type date},
{"Amount", type number}
}),
Filtered = Table.SelectRows(TypedCols, each [Amount] > 0)
in
Filtered
M supports single-line and multi-line comments.
// This is a single-line comment
/* This is a
multi-line comment */
Integer and decimal number literals.
42 // integer
3.14 // decimal
-100 // negative
1.5e3 // scientific notation = 1500
Text values are enclosed in double quotes. Use #(lf) for a line feed, #(tab) for a tab.
"Hello World"
"Line 1#(lf)Line 2" // newline inside text
"Tab#(tab)separated"
Boolean values — always lowercase in M.
true
false
Represents the absence of a value.
null
Date literals use the #date constructor.
#date(2024, 6, 15) // June 15, 2024
Datetime literals use the #datetime constructor.
#datetime(2024, 6, 15, 9, 30, 0) // June 15 2024 09:30:00
Duration literals use the #duration constructor — days, hours, minutes, seconds.
#duration(1, 2, 30, 0) // 1 day, 2 hours, 30 minutes
#duration(0, 0, 0, 45) // 45 seconds
An ordered collection of values, enclosed in curly braces.
{1, 2, 3, 4, 5}
{"apple", "banana", "cherry"}
{1, "mixed", true, null} // lists can hold mixed types
{1..10} // range shorthand: 1 to 10
A set of named fields enclosed in square brackets. Like a row or a JSON object.
[Name = "Alex", Age = 30, Active = true]
A structured collection of rows and columns. Usually produced by a data source function or a Table.From* function.
#table(
{"Name", "Sales"},
{
{"North", 42000},
{"South", 38000},
{"East", 51000}
}
)
The foundation of every M query. Each step in let is a named variable referencing the previous step.
let
RawData = Excel.Workbook(File.Contents("C:\sales.xlsx"), true),
SalesSheet = RawData{[Item="Sales", Kind="Sheet"]}[Data],
Promoted = Table.PromoteHeaders(SalesSheet),
Cleaned = Table.SelectRows(Promoted, each [Region] <> null)
in
Cleaned
Step names can contain spaces if wrapped in #"...". Descriptive names make queries readable.
let
#"Raw Source" = Csv.Document(...),
#"Promoted Headers" = Table.PromoteHeaders(#"Raw Source"),
#"Removed Nulls" = Table.SelectRows(#"Promoted Headers", each [ID] <> null)
in
#"Removed Nulls"
Each step is just a variable. You reference a previous step by name to build on it.
let
Source = ...,
Step2 = Table.PromoteHeaders(Source), // references Source
Step3 = Table.SelectRows(Step2, ...) // references Step2
in
Step3
Standard math operators.
5 + 3 // 8
10 - 4 // 6
6 * 7 // 42
15 / 4 // 3.75
Return true or false. M comparisons are type-sensitive.
5 > 3 // true
5 < 3 // false
5 = 5 // true (single = for equality)
5 <> 3 // true (not equal)
5 >= 5 // true
5 <= 4 // false
Combine boolean expressions.
true and false // false
true or false // true
not true // false
Join two text values with &.
"Hello" & " " & "World" // "Hello World"
"FY" & Text.From(2024) // "FY2024"
Combine two lists with &.
{1, 2, 3} & {4, 5, 6} // {1, 2, 3, 4, 5, 6}
{"a", "b"} & {"c"} // {"a", "b", "c"}
is tests whether a value is of a type. as asserts a type (errors if wrong).
42 is number // true
"hello" is text // true
42 is text // false
42 as number // 42
"hello" as number // error
Append ? to a type to allow null values without error.
42 as number? // 42
null as number? // null (no error)
Return the number of characters in a text value.
Text.Length("Hello") // 5
Text.Length([ProductCode]) // length of each code
Convert text to uppercase, lowercase, or title case.
Text.Upper("hello world") // "HELLO WORLD"
Text.Lower("HELLO WORLD") // "hello world"
Text.Proper("hello world") // "Hello World"
Extract N characters from the start or end of a string.
Text.Start("SKU-001-A", 3) // "SKU"
Text.End("SKU-001-A", 1) // "A"
Extract a substring by start position and length (zero-indexed).
// Text.Middle(text, start, count)
Text.Middle("SKU-001-A", 4, 3) // "001"
Extract a substring by start position and optional length (zero-indexed).
Text.Range("Hello World", 6) // "World"
Text.Range("Hello World", 0, 5) // "Hello"
Split a text string into a list using a delimiter.
Text.Split("North,South,East", ",") // {"North", "South", "East"}
Text.Split("2024-06-15", "-") // {"2024", "06", "15"}
Join a list of text values into a single string with an optional separator.
Text.Combine({"North", "South", "East"}, ", ") // "North, South, East"
Text.Combine({"2024", "06", "15"}, "-") // "2024-06-15"
Replace all occurrences of a substring with another.
Text.Replace("Hello World", "World", "M Code") // "Hello M Code"
Text.Replace([Phone], "-", "") // remove dashes
Return true if a text value contains a substring.
Text.Contains("Power BI Desktop", "BI") // true
Text.Contains("Power BI Desktop", "DAX") // false
Return true if text starts or ends with a given value.
Text.StartsWith("SKU-001", "SKU") // true
Text.EndsWith("report.csv", ".csv") // true
Remove whitespace. Trim removes both sides; TrimStart removes leading; TrimEnd removes trailing.
Text.Trim(" Hello World ") // "Hello World"
Text.TrimStart(" Hello World ") // "Hello World "
Text.TrimEnd(" Hello World ") // " Hello World"
Pad a string to a minimum length with a specified character.
Text.PadStart("42", 5, "0") // "00042"
Text.PadEnd("North", 10, " ") // "North "
Convert any value to its text representation.
Text.From(42) // "42"
Text.From(3.14) // "3.14"
Text.From(true) // "true"
Text.From(#date(2024,6,15)) // "2024-06-15"
Convert a text string into a list of individual characters.
Text.ToList("ABC") // {"A", "B", "C"}
Return the absolute value.
Number.Abs(-42) // 42
Number.Abs(42) // 42
Round to a specified number of decimal places.
Number.Round(3.14159, 2) // 3.14
Number.Round(2.5, 0) // 3
Always round up or always round down.
Number.RoundUp(3.1, 0) // 4
Number.RoundDown(3.9, 0) // 3
Return the integer quotient of a division (no remainder).
Number.IntegerDivide(17, 5) // 3
Return the remainder of a division.
Number.Mod(17, 5) // 2
Number.Mod(10, 2) // 0
Raise a number to a power.
Number.Power(2, 10) // 1024
Number.Power(9, 0.5) // 3 (square root)
Return the square root.
Number.Sqrt(144) // 12
Number.Sqrt(2) // 1.4142...
Natural logarithm and its inverse (e to the power of x).
Number.Ln(1) // 0
Number.Exp(1) // 2.71828... (e)
Logarithm with a specified base.
Number.Log(100, 10) // 2
Number.Log(8, 2) // 3
Return true if a value is Not a Number (NaN).
Number.IsNaN(0/0) // true
Number.IsNaN(42) // false
Convert a value to a number.
Number.From("42") // 42
Number.From(true) // 1
Number.From(false) // 0
Convert a number to text with optional format and culture.
Number.ToText(1234567.89, "N2") // "1,234,567.89"
Number.ToText(0.852, "P1") // "85.2%"
Test deep equality between two values of any type.
Value.Equals({1,2,3}, {1,2,3}) // true
Value.Equals([A=1], [A=1]) // true
Convert a value to a date.
Date.From("2024-06-15") // #date(2024, 6, 15)
Date.From(#datetime(2024,6,15,9,0,0)) // #date(2024, 6, 15)
Return the current local date and time.
DateTime.LocalNow() // current datetime on the machine
Extract the year, month, or day from a date.
Date.Year(#date(2024, 6, 15)) // 2024
Date.Month(#date(2024, 6, 15)) // 6
Date.Day(#date(2024, 6, 15)) // 15
Return the day of week as a number. Optional second argument sets the first day of the week.
// Day.Monday makes Monday = 0
Date.DayOfWeek(#date(2024, 6, 15), Day.Monday) // returns 5 (Saturday)
Return the week number of the year.
Date.WeekOfYear(#date(2024, 6, 15)) // 24
Return the quarter number (1–4).
Date.QuarterOfYear(#date(2024, 6, 15)) // 2
Add a specified number of days, months, or years to a date.
Date.AddDays(#date(2024, 6, 15), 30) // #date(2024, 7, 15)
Date.AddMonths(#date(2024, 6, 15), 3) // #date(2024, 9, 15)
Date.AddYears(#date(2024, 6, 15), 1) // #date(2025, 6, 15)
Return the first or last day of the month.
Date.StartOfMonth(#date(2024, 6, 15)) // #date(2024, 6, 1)
Date.EndOfMonth(#date(2024, 6, 15)) // #date(2024, 6, 30)
Return the first or last day of the year.
Date.StartOfYear(#date(2024, 6, 15)) // #date(2024, 1, 1)
Date.EndOfYear(#date(2024, 6, 15)) // #date(2024, 12, 31)
Extract components from a duration value.
Duration.Days(#duration(3, 4, 30, 0)) // 3
Duration.TotalHours(#duration(1, 6, 0, 0)) // 30
Subtract two dates to get a duration, then extract the component you need.
let
Start = #date(2024, 1, 1),
End = #date(2024, 6, 15),
Diff = End - Start,
DaysApart = Duration.Days(Diff) // 166
in
DaysApart
Create lists with literal syntax or range shorthand.
{1, 2, 3} // explicit list
{1..10} // range: 1 to 10
{"a".."e"} // character range: {"a","b","c","d","e"}
{} // empty list
Return the number of items in a list.
List.Count({10, 20, 30, 40}) // 4
Return the first or last item.
List.First({10, 20, 30}) // 10
List.Last({10, 20, 30}) // 30
Return the first or last N items as a list.
List.FirstN({10, 20, 30, 40, 50}, 3) // {10, 20, 30}
List.LastN({10, 20, 30, 40, 50}, 2) // {40, 50}
Remove duplicate values.
List.Distinct({"a", "b", "a", "c", "b"}) // {"a", "b", "c"}
Filter a list to items that satisfy a condition.
List.Select({1, 2, 3, 4, 5, 6}, each _ > 3) // {4, 5, 6}
List.Select({"apple","ant","banana"}, each Text.StartsWith(_, "a"))
// {"apple", "ant"}
Apply a function to every item in a list and return the results.
List.Transform({1, 2, 3, 4}, each _ * 2) // {2, 4, 6, 8}
List.Transform({"hello", "world"}, Text.Upper) // {"HELLO", "WORLD"}
Sort a list. Default is ascending; pass Order.Descending for descending.
List.Sort({3, 1, 4, 1, 5, 9}) // {1, 1, 3, 4, 5, 9}
List.Sort({3, 1, 4, 1, 5, 9}, Order.Descending) // {9, 5, 4, 3, 1, 1}
Reverse the order of a list.
List.Reverse({1, 2, 3, 4, 5}) // {5, 4, 3, 2, 1}
Test whether a list contains a value, any value from another list, or all values.
List.Contains({"a","b","c"}, "b") // true
List.ContainsAny({"a","b","c"}, {"b","x"}) // true (b matches)
List.ContainsAll({"a","b","c"}, {"a","b"}) // true
Test whether any or all items in a list satisfy a condition.
List.MatchesAny({1, 2, 3, 4}, each _ > 3) // true (4 > 3)
List.MatchesAll({1, 2, 3, 4}, each _ > 0) // true (all positive)
Remove all null values from a list.
List.RemoveNulls({1, null, 2, null, 3}) // {1, 2, 3}
Remove a range of items by start index and count.
// List.RemoveRange(list, index, count)
List.RemoveRange({1, 2, 3, 4, 5}, 1, 2) // {1, 4, 5}
Repeat a list N times.
List.Repeat({1, 2}, 3) // {1, 2, 1, 2, 1, 2}
Concatenate multiple lists into one.
List.Combine({{1, 2}, {3, 4}, {5, 6}}) // {1, 2, 3, 4, 5, 6}
Split a list into sublists of a specified size.
List.Split({1, 2, 3, 4, 5, 6}, 2)
// {{1, 2}, {3, 4}, {5, 6}}
Aggregate functions over a list.
List.Min({3, 1, 4, 1, 5}) // 1
List.Max({3, 1, 4, 1, 5}) // 5
List.Sum({10, 20, 30}) // 60
List.Average({10, 20, 30}) // 20
Multiply all values or calculate standard deviation.
List.Product({2, 3, 4}) // 24
List.StandardDeviation({2,4,4,4,5,5,7,9}) // 2
Set operations on lists.
List.Union({{1,2,3},{2,3,4}}) // {1, 2, 3, 4}
List.Intersect({{1,2,3},{2,3,4}}) // {2, 3}
List.Difference({1,2,3,4},{2,4}) // {1, 3}
Generate a list of numbers from a start, with a count and optional step.
List.Numbers(1, 5) // {1, 2, 3, 4, 5}
List.Numbers(0, 5, 2) // {0, 2, 4, 6, 8}
Generate a list of dates from a start, with a count and duration step.
List.Dates(
#date(2024, 1, 1),
12,
#duration(31, 0, 0, 0)
)
// Approximately one date per month for 12 months
Generate a list by iterating with an initial state, condition, and next-state function.
// Generate powers of 2 up to 512
List.Generate(
() => 1,
each _ <= 512,
each _ * 2
)
// {1, 2, 4, 8, 16, 32, 64, 128, 256, 512}
Return the single item in a list, or a default value if the list is empty or has multiple items.
List.SingleOrDefault({42}) // 42
List.SingleOrDefault({}, 0) // 0
List.SingleOrDefault({1,2}, -1) // -1 (multiple items)
Create a record with named fields using square bracket syntax.
[Name = "Alex", Role = "Engineer", Active = true]
Access a field value by name as a function call.
Record.Field([Name = "Alex", Age = 30], "Name") // "Alex"
Access a field value using dot-style lookup — more common in practice.
let r = [Name = "Alex", Age = 30]
in r[Name] // "Alex"
Return a list of all field names or all field values.
Record.FieldNames([A = 1, B = 2, C = 3]) // {"A", "B", "C"}
Record.FieldValues([A = 1, B = 2, C = 3]) // {1, 2, 3}
Return true if a record contains the specified field(s).
Record.HasFields([A = 1, B = 2], "A") // true
Record.HasFields([A = 1, B = 2], {"A", "C"}) // false (C missing)
Add a new field to a record.
Record.AddField([Name = "Alex"], "Role", "Engineer")
// [Name = "Alex", Role = "Engineer"]
Remove one or more fields from a record.
Record.RemoveFields([A = 1, B = 2, C = 3], {"B", "C"})
// [A = 1]
Rename fields by providing a list of old/new name pairs.
Record.RenameFields(
[first_name = "Alex", last_name = "Mensah"],
{{"first_name", "FirstName"}, {"last_name", "LastName"}}
)
// [FirstName = "Alex", LastName = "Mensah"]
Convert a record to a two-column table with Name and Value columns.
Record.ToTable([Region = "North", Sales = 42000])
// Table: Name | Value
// Region | North
// Sales | 42000
Create a record from a list of values and a list of field names.
Record.FromList({"Alex", 30, true}, {"Name", "Age", "Active"})
// [Name = "Alex", Age = 30, Active = true]
Create a table from a list of records. Each record becomes a row.
Table.FromRecords({
[Name = "Alex", Sales = 42000],
[Name = "Sarah", Sales = 51000],
[Name = "James", Sales = 38000]
})
Return the number of rows or columns.
Table.RowCount(Source) // e.g. 1500
Table.ColumnCount(Source) // e.g. 12
Return a list of column names.
Table.ColumnNames(Source) // {"ID", "Name", "Date", "Amount"}
Filter rows using a condition. each is shorthand for a row function.
Table.SelectRows(Source, each [Region] = "North")
Table.SelectRows(Source, each [Amount] > 1000 and [Status] = "Active")
Add a new calculated column.
Table.AddColumn(Source, "FullName", each [FirstName] & " " & [LastName])
Table.AddColumn(Source, "Margin",
each [Revenue] - [Cost],
type number
)
Apply a transformation function to one or more existing columns.
Table.TransformColumns(Source, {
{"Name", Text.Proper},
{"Email", Text.Lower},
{"Amount", each _ * 1.1, type number}
})
Set the data type of one or more columns.
Table.TransformColumnTypes(Source, {
{"OrderDate", type date},
{"Amount", type number},
{"CustomerID", type text}
})
Remove one or more columns by name.
Table.RemoveColumns(Source, {"TempCol", "InternalID"})
Keep only the specified columns — drops everything else.
Table.SelectColumns(Source, {"CustomerID", "Name", "OrderDate", "Amount"})
Rename columns by providing old/new name pairs.
Table.RenameColumns(Source, {
{"customer_id", "CustomerID"},
{"order_date", "OrderDate"},
{"total_amount", "TotalAmount"}
})
Reorder columns to a specified sequence.
Table.ReorderColumns(Source, {"ID", "Date", "Name", "Amount"})
Sort a table by one or more columns.
Table.Sort(Source, {{"OrderDate", Order.Descending}})
// Multiple sort columns
Table.Sort(Source, {
{"Region", Order.Ascending},
{"Amount", Order.Descending}
})
Group rows and apply aggregations — equivalent to GROUP BY in SQL.
Table.Group(Source, {"Region"}, {
{"TotalSales", each List.Sum([Amount]), type number},
{"OrderCount", each Table.RowCount(_), type number},
{"AvgSale", each List.Average([Amount]), type number}
})
Use the first row of a table as column headers.
Table.PromoteHeaders(Source, [PromoteAllScalars = true])
Move column headers back to the first row as data.
Table.DemoteHeaders(Source)
Fill null values in a column by propagating the nearest non-null value downward or upward.
Table.FillDown(Source, {"Region", "Category"})
Table.FillUp(Source, {"Region"})
Pivot a column — turn unique row values into column headers.
// Turn Region values (North, South, East) into columns
Table.Pivot(
Source,
List.Distinct(Source[Region]), // distinct values become column names
"Region", // column to pivot
"Amount", // values to aggregate
List.Sum // aggregation function
)
Unpivot specified columns — turn column headers into row values. Reverse of pivot.
Table.Unpivot(Source, {"Jan","Feb","Mar","Apr"}, "Month", "Amount")
// Columns Jan, Feb, Mar, Apr become rows in Month column
Unpivot all columns except the ones specified as identity columns.
Table.UnpivotOtherColumns(Source, {"ProductID", "ProductName"}, "Month", "Amount")
Expand a column containing lists — each list item becomes its own row.
Table.ExpandListColumn(Source, "Tags")
Expand a column containing records — each record field becomes its own column.
Table.ExpandRecordColumn(
Source,
"Address",
{"Street", "City", "PostalCode"},
{"Address.Street", "Address.City", "Address.PostalCode"}
)
Join two tables on a key column and store the result as a new nested table column.
Table.NestedJoin(
Orders, {"CustomerID"},
Customers, {"CustomerID"},
"CustomerData",
JoinKind.LeftOuter
)
Join two tables directly — returns a flat combined table.
Table.Join(
Orders, {"CustomerID"},
Customers, {"CustomerID"},
JoinKind.Inner
)
Remove duplicate rows. Optionally specify columns to compare.
Table.Distinct(Source)
Table.Distinct(Source, {"CustomerID", "OrderDate"})
Load the entire table into memory — prevents repeated re-evaluation of upstream steps. Use before operations that iterate over the table multiple times.
Buffered = Table.Buffer(Source)
Define a reusable function using the (parameters) => expression syntax.
// Named function in a let block
AddTax = (price as number, rate as number) as number =>
price * (1 + rate),
// Call it
TotalWithTax = AddTax(100, 0.2) // 120
each is shorthand for (_) => — a function that takes a single argument called _. Used heavily with table and list functions.
// These are equivalent
Table.SelectRows(Source, each [Amount] > 100)
Table.SelectRows(Source, (_) => _[Amount] > 100)
// each in List.Transform
List.Transform({1, 2, 3, 4}, each _ * 2)
Inside each, use [FieldName] to access a field of the current row record.
// Access multiple fields per row
Table.AddColumn(Source, "Label",
each [Region] & " - " & Text.From([Year])
)
// Conditional per row
Table.AddColumn(Source, "Tier",
each if [Amount] >= 10000 then "High"
else if [Amount] >= 5000 then "Mid"
else "Low"
)
Specify input and output types on a function for validation and documentation.
CleanName = (raw as text) as text =>
Text.Trim(Text.Proper(raw))
Mark a parameter optional with optional. It receives null if not provided.
Greet = (name as text, optional greeting as text) as text =>
let
g = if greeting = null then "Hello" else greeting
in
g & ", " & name & "!"
Greet("Alex") // "Hello, Alex!"
Greet("Alex", "Welcome") // "Welcome, Alex!"
Return the left value if it is not null, otherwise return the right value.
"value" ?? "default" // "value"
null ?? "default" // "default"
// Practical use — fallback when a field might be null
[PreferredName] ?? [FirstName] ?? "Unknown"
Part of the FTTG Learn Cheat Sheet series — fttgsolutions.com