Power Query glossary
41 terms, each linked to the lesson that teaches it.
- Aggregation
- A calculation over many values that returns one, such as sum, count, minimum, maximum or average.
- Anti join
- A merge that returns rows with no match: left anti for rows only in the first table, right anti for rows only in the second.
- Append
- Stacking rows of two or more tables into one, matching columns by name.
- Applied steps
- The ordered list of transformations in a query. Each step is a named M expression that usually takes the previous step as input, and the whole list reruns on every refresh.
- Banker's rounding
- Rounding halves to the nearest even digit (2.5 → 2, 3.5 → 4). It's Number.Round's default mode.
- Composite key
- A key made of several columns that together identify a row, such as Store + Product for a price list.
- Conditional column
- A column whose value is chosen by if … then … else tests evaluated top to bottom.
- Count distinct
- The number of different values, as opposed to the number of rows: List.Count(List.Distinct(list)).
- Culture (locale)
- Regional conventions such as en-GB or de-DE that decide how text like 05/03/2026 or 1.234,5 is read when converting to dates and numbers.
- Custom function
- A reusable function you define in M with =>, such as (qty, region) => …, then invoke from other steps.
- Data type
- What a column's values mean: text, whole number, decimal, date, logical and so on. Types drive comparisons, sorting and what downstream tools see.
- Deduplicate
- Removing duplicate rows, either comparing whole rows or only key columns.
- Delimiter
- The character that separates fields in a text file, typically a comma, semicolon or tab.
- each
- Shorthand for a function with one parameter named _. each [Qty] * 2 means (_) => _[Qty] * 2.
- Encoding
- How characters are stored as bytes in a file. UTF-8 (code page 65001) is the usual choice; the wrong encoding garbles accented letters.
- Error value
- A cell or value that records a failure, such as converting "n/a" to a number. Errors stay in their cell until a step needs the value.
- Expand
- Turning a nested table, record or list column into ordinary columns or rows.
- Fill down
- Replacing nulls with the nearest value above, used to repair report layouts that print a label once per group.
- Formula firewall
- The mechanism that enforces privacy levels, raising an error when a query combines sources in a way that could leak data.
- Group By
- Collapsing rows that share key values into one row per group, with aggregations like sum, count or average.
- Index column
- A column of running numbers, added in the current row order.
- Join explosion
- An unexpected increase in row count after a merge, caused by duplicate keys in the lookup table.
- Join kind
- Which rows a merge keeps: left outer, inner, left anti, right anti, full outer or right outer.
- Left outer join
- A merge that keeps every row of the first table and adds matches from the second, with null where there's no match. The usual choice for lookups.
- let expression
- An M construct that binds names to values and returns one of them after in. Applied steps are the bindings of a let expression.
- List
- An ordered sequence of values, written {1, 2, 3}. Table[Column] returns a column as a list.
- M language
- Power Query's formula language. It's functional and case-sensitive: every query is an expression, typically a let … in block, that produces a value such as a table.
- Merge
- Power Query's join: matching rows of two tables on key columns and bringing matching rows across as a nested table.
- null
- M's 'no value'. Arithmetic with null returns null, comparisons with null return null, and filters drop rows whose condition is null.
- Pivot
- Turning distinct values of one column into new columns, filled from a value column with an aggregation.
- Privacy levels
- Labels (Private, Organizational, Public) that stop Power Query from sending data from one source to another.
- Promote headers
- Turning the first row of a table into its column names, usually right after importing a CSV.
- Query folding
- Translating Power Query steps into the source's own query language (often SQL) so the source does the work.
- Record
- A set of named fields, written [Name = "Ana", Age = 31]. A table row is a record, and JSON objects become records.
- Refresh
- Rerunning a query's applied steps against the current source data. Robust queries give correct results for data they haven't seen before.
- Schema drift
- Changes to a source's columns over time, such as new, renamed or removed columns, which brittle queries don't handle.
- Staging query
- A query that only loads and lightly cleans one source, used as input to other queries. It keeps combining logic separate.
- Table
- Rows of records sharing the same columns. Most Power Query steps take a table and return a table.
- try … otherwise
- M's error handling: try runs an expression and otherwise supplies a fallback if it errors.
- Underscore (_)
- The implicit parameter created by each. In row functions it's the current row record; in Table.TransformColumns it's the current cell value.
- Unpivot
- Turning columns into attribute/value rows, for example one column per month into Month and Units rows.