Power QueryExercises

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

MeasureResult
Items5
Over 10 after tax15, 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

ExpressionValue
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.

Without ?: 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

ProductPrice
Kite19
Reel6

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.
Functions in this lesson:List.TransformList.SelectList.NumbersTable.FromColumnsList.Count

Next step

Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.

Take the M language basics quiz