Power QueryExercises

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.