Lists in depth: build, transform, select and access
Use list functions confidently to compute values that would be awkward with table steps.
~10 min read · intermediate · Module 16: M language basics
By Tahir Nas · Examples run in our simulator · Updated
Before this: M basics: let, records and lists
Lists are everywhere in M: a column is a list, Table.ColumnNames returns a list, and many functions take lists of columns or pairs. A handful of list functions covers most needs.
Building lists
- Literal:
{"North", "South"}. - Range:
{1..5}is 1, 2, 3, 4, 5;{"a".."e"}works for single characters. - Generated:
List.Numbers(0, 4, 10)is 0, 10, 20, 30. - From a table:
Orders[Qty].
Transform and select
List.Transform applies a function to every item; List.Select keeps the items for which a function returns true. Together they replace many loops:
let
Prices = {12.5, 4, 3.5, 20, null},
WithTax = List.Transform(Prices, each if _ = null then null else Number.Round(_ * 1.2, 2)),
Over10 = List.Select(WithTax, each _ <> null and _ > 10)
in
#table({"Measure", "Result"}, {
{"Items", List.Count(Prices)},
{"Over 10 after tax", Text.Combine(List.Transform(Over10, Text.From), ", ")}
})Result
| Measure | Result |
|---|---|
| Items | 5 |
| Over 10 after tax | 15, 24 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Positions and safe access
List{0} is the first item. Asking for a position that doesn't exist raises an error; adding ? returns null instead, which is handy when a list may be short:
let
Parts = Text.Split("Leeds-04", "-")
in
#table({"Expression", "Value"}, {
{"Parts{0}", Parts{0}},
{"Parts{1}", Parts{1}},
{"Parts{2}?", Parts{2}?},
{"List.Last(Parts)", List.Last(Parts)}
})Result
| Expression | Value |
|---|---|
| Parts{0} | Leeds |
| Parts{1} | 04 |
| Parts{2}? | null |
| List.Last(Parts) | 04 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
{"Leeds", "04"}{2}Result: an error
Expression.Error: There weren't enough elements in the enumeration to complete the operation.
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
?: There weren't enough elements in the enumeration to complete the operation.Lists to tables and back
Table.FromColumns builds a table from lists of equal length, one per column, and Table.ToRows / Table.Column go the other way. That's useful for small lookup tables built in code:
Table.FromColumns({{"Kite", "Reel"}, {19, 6}}, {"Product", "Price"})Result
| Product | Price |
|---|---|
| Kite | 19 |
| Reel | 6 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Where to find this in Excel
There's no button for this. In Excel you write it in the Power Query editor's Advanced Editor (View > Advanced Editor, also on the Home tab), which shows the whole query as M.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Use Power Query to transform data (the Power Query editor) (Microsoft Learn).
Common mistakes
- Accessing
{1}for the first item. - Using positional access on a list that may be shorter, without
?or a length check. - Writing List.Transform with a function that fails on null items.
Excel, Power BI and the simulator
- Excel and Power BI Desktop show a list in the editor as a single column with a To Table button; List Tools appear on the ribbon.
- Clicking a column header's drill-down (right-click → Drill Down) creates the
Source[Column]list expression for you. - In the simulator, every item of a list is evaluated when the list is built, so an error item fails the whole list (KD07); native M evaluates list items lazily.
List.TransformList.SelectList.NumbersTable.FromColumnsList.CountNext step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the M language basics quiz