Power QueryExercises

Read the M behind the Applied Steps

Read a generated query and say what each step does and which step it depends on.

~10 min read · beginner · Module 1: How Power Query thinks

By Tahir Nas · Examples run in our simulator · Updated

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

You don't have to write M to use Power Query, but being able to read it pays off quickly. When a step fails, the formula bar shows its M, and the error message names functions and columns. This lesson maps the step names the editor generates to the functions behind them.

Step names and their functions

Step name in the paneFunctionWhat it does
Promoted HeadersTable.PromoteHeadersFirst row becomes the column names
Changed TypeTable.TransformColumnTypesSets column types
Filtered RowsTable.SelectRowsKeeps rows matching a condition
Removed Other ColumnsTable.SelectColumnsKeeps only the listed columns
Renamed ColumnsTable.RenameColumnsRenames from {old, new} pairs
Sorted RowsTable.SortOrders rows by one or more keys
Added CustomTable.AddColumnAdds a calculated column
Grouped RowsTable.GroupSummarises rows per key
Merged QueriesTable.NestedJoinJoins another table as a nested column
Unpivoted Other ColumnsTable.UnpivotOtherColumnsTurns columns into attribute/value rows

The step name is only a label; you can rename it freely. The function is what runs. A step called Removed Other Columns actually calls Table.SelectColumns, which is why it keeps a fixed list of columns.

Quoted names and comments

Step names with spaces are written #"Added Custom". Names without spaces or special characters, such as Source, need no quotes. Lines starting with // are comments; the engine ignores them, and they're a good place to record why a step exists.

Steps don't have to use the previous step

Most steps take the step directly above as input, but any step may refer to any earlier name. Here a helper step computes a total from Source, and the last step uses both:

let
    Source = #table({"Item", "Qty"}, {{"Kite", 4}, {"Reel", 1}, {"Line", 5}}),
    // Grand total of the whole source, used by the share calculation below
    TotalQty = List.Sum(Source[Qty]),
    #"Added Share" = Table.AddColumn(Source, "Share", each [Qty] / TotalQty, type number)
in
    #"Added Share"

Result

ItemQtyShare
Kite40.4
Reel10.1
Line50.5

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

TotalQty is a number, not a table, and it appears as its own step in the pane.

When you read an unfamiliar query, trace these references. A step that isn't used by anything later is dead code, and a step referenced by several others is a natural place to add a check.

A reading routine

  1. Start at in to see which step is returned.
  2. Walk upwards, noting each step's input name.
  3. For each step, name the function and its key arguments: which columns, which condition.
  4. Look for hard-coded values (column lists, dates, file paths) that may break on refresh.

Where to find this in Excel

Excel for Windows
Power Query editor > View > Advanced Editor (also Home > Advanced Editor)
What you see
A window shows the whole query as M, one named step per line, and tells you if the syntax is valid.

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); Using custom functions (Microsoft Learn).

How to open the Power Query editor in Excel

Common mistakes

  • Trusting the step name: a step renamed to Filter completed could contain any function.
  • Assuming every step uses the one directly above it, and missing a step that references Source again.
  • Deleting a step that looks unused in the pane but is referenced by a later step's formula.

Excel, Power BI and the simulator

  • The formula bar (View → Formula Bar) shows one step's M; the Advanced Editor shows the whole query. Both exist in Excel and Power BI Desktop.
  • Step names generated by the editor follow the host's display language, so a German Excel shows different default names for the same functions.
  • In the simulator, the M editor shows the whole query and the step list updates from the let bindings.
Functions in this lesson:List.SumTable.AddColumn

Next step

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

Take the Power Query basics quiz