Power QueryExercises

M basics: let, records and lists

Write small M expressions with let, lists and records.

~11 min read · beginner · Module 16: M language basics

By Tahir Nas · Examples run in our simulator · Published · Updated

Before this: Applied steps are a recipe, not a one-off edit

M is a functional language: everything is an expression that produces a value. A query is usually one big let … in expression, and the Applied Steps pane is a list of its bindings.

Values

KindExample
Number42, 3.5
Text"North"
Logicaltrue
nullnull
Date#date(2026, 3, 14)
List{1, 2, 3}
Record[Name = "Ana", Age = 31]
Table#table({"A"}, {{1}, {2}})

let … in

Each line in a let binds a name to a value; in says which value the whole expression returns. Names can be used by later lines. Evaluation is lazy: a binding that nothing needs is never computed.

Columns as lists

Orders[Qty] returns the Qty column as a list, ready for list functions. A one-row summary table is a good way to see several of them at once:

let
    Orders = #table({"OrderID", "Qty", "UnitPrice"}, {{"A-1", 2, 12.5}, {"A-2", 6, 4}, {"A-3", 4, 3.5}}),
    Total = List.Sum(Orders[Qty]),
    Biggest = List.Max(Orders[UnitPrice]),
    Count = Table.RowCount(Orders),
    Result = #table({"Orders", "Total Qty", "Max Price"}, {{Count, Total, Biggest}})
in
    Result

Result

OrdersTotal QtyMax Price
31212.5

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Records and field access

Records use square brackets for fields: [Name = "Ana"][Name] is "Ana". A table row is a record, which is why [Qty] inside each reads a field of the current row. Lists use curly braces for positions, counting from zero:

let
    Customer = [Name = "Ana", City = "Leeds", Orders = {"A-1", "A-3"}],
    Orders = #table({"OrderID", "Qty"}, {{"A-1", 2}, {"A-2", 6}})
in
    #table({"Expression", "Value"}, {
        {"Customer[City]", Customer[City]},
        {"Customer[Orders]{1}", Customer[Orders]{1}},
        {"Orders{0}[Qty]", Orders{0}[Qty]},
        {"Orders[OrderID]{1}", Orders[OrderID]{1}}
    })

Result

ExpressionValue
Customer[City]Leeds
Customer[Orders]{1}A-3
Orders{0}[Qty]2
Orders[OrderID]{1}A-2

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Orders{0} is the first row (a record); [Qty] then reads its field.

Where to find this in Excel

Start from Data > Get Data > From Other Sources > Blank Query, then write the let expression in the Advanced Editor (View > Advanced Editor).

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Create, load, or edit a query in Excel (Power Query) (Microsoft Support); Use Power Query to transform data (the Power Query editor) (Microsoft Learn).

How to open the Power Query editor in Excel

Common mistakes

  • Mixing up {} (lists, positions) and [] (records, fields).
  • Counting list positions from 1; {0} is the first item.
  • Expecting a step that nothing references to run; unused bindings are never evaluated.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop: View → Advanced Editor shows the full let expression; Home → New Source → Blank Query gives you an empty one to experiment in.
  • Typing = List.Sum({1, 2, 3}) into a blank query's formula bar is a quick way to try an expression in either host.
  • The simulator's M editor and playground evaluate the same expressions; unsupported syntax (such as section documents) is listed on the compatibility page.
Functions in this lesson:List.SumList.MaxTable.RowCount#table

Next step

Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.

Practise: Build a one-row summary table in M (kite shop)

Practice this

Each exercise runs in the browser and is checked against fresh data, not just the example.