[Excel/Power Query] Error: Expression.Error — Token RightBracket Expected (missing “]”) — How to Fix It

Summary

Expression.Error: Token RightBracket Expected means an opening square bracket [ in your M code was never closed with ]. This typically happens in column or record references, especially when headers contain spaces/special characters or when nested structures are edited manually. The fix is mechanical: locate the highlighted spot, balance every [ … ], and use the correct syntax for spaced names ([#"Column Name"]). Formatting the query and reviewing the last edits usually reveals the unmatched bracket fast.

Context

Power Query’s M language is tolerant of whitespace but strict about delimiters. Square brackets denote record and column access (e.g., [Amount] or [#"Total Amount"]). In complex expressions—such as Table.AddColumn with nested field reads, Record.Field calls, or dynamic Table.ExpandTableColumn—it’s easy to remove or misplace a bracket while refactoring. The parser stops where it first notices the structural mismatch, often near each or at the end of the step, even though the real omission is a few characters earlier.

Probable Cause

  • Unclosed field reference: A column/record access started with [ but lacked a matching ], e.g., [Amount inside a custom column.
  • Incorrect quoting for spaced names: Using [Total Amount] instead of [#"Total Amount"] (M is case- and character-sensitive for field names).
  • Nested records/tables edited by hand: Mixed Record.Field([Col],"Key") and bracket access where one bracket was dropped during copy/paste.
  • Extra tokens interrupting access: Trailing commas, comments, or operators inserted before closing the bracket.

Quick Fix

  1. Open Advanced Editor: Home → Advanced Editor. Power Query highlights the first suspicious position; scroll a few characters before it.
  2. Format the code: Press Ctrl+Shift+F to reindent. Unbalanced [ … ] blocks become visually obvious.
  3. Close every bracketed reference: For plain headers, use [Amount]; for headers with spaces/special chars, use [#"Total Amount"].
  4. Normalize complex reads: Prefer explicit functions when clarity helps, e.g., Record.Field(_, "Amount"), then rewrap with brackets if desired.
  5. Re-run the step: After balancing, refresh the preview. If the cursor now jumps elsewhere, repeat for the next unmatched delimiter.
// ❌ Missing right bracket
Table.AddColumn(Source, "Net", each [Revenue] - [Cost)

// ✅ Balanced brackets
Table.AddColumn(Source, "Net", each [Revenue] - [Cost])

// ❌ Spaced header not quoted correctly
each [Total Amount] * 0.2

// ✅ Correct quoting for spaced name
each [#"
Total Amount"] * 0.2

Full Example

Scenario: You add a custom column computing markup from “Total Amount” and “Tax Rate”. After editing, the step fails with Token RightBracket Expected near ‘each’.

// ❌ Failing step
let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    Typed  = Table.TransformColumnTypes(Source, {{"Total Amount", type number}, {"Tax Rate", type number}}),
    AddCol = Table.AddColumn(Typed, "WithTax",
              each [#"
Total Amount"] * (1 + [Tax Rate])   // <-- missing closing bracket after [Tax Rate
    )
in
    AddCol

Fix: Close the bracket in the second field reference and keep quoting for the spaced header.

// ✅ Corrected
let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    Typed  = Table.TransformColumnTypes(Source, {{"Total Amount", type number}, {"Tax Rate", type number}}),
    AddCol = Table.AddColumn(Typed, "WithTax",
              each [#"
Total Amount"] * (1 + [#"
Tax Rate"])
    )
in
    AddCol

Alternative (explicit record access): When debugging, rewrite the body to avoid nested brackets until balanced.

each Record.Field(_, "Total Amount") * (1 + Record.Field(_, "Tax Rate"))

Pitfalls & Debug

  • Symptom → Error points at each. Fix → The missing ] is usually a few characters before; scan left for the last [ without closure.
  • Symptom → Works for simple headers, fails for spaced ones. Fix → Use [#"Header With Spaces"] or normalize headers with Table.TransformColumnNames early.
  • Symptom → Breaks after pasting code from web. Fix → Hidden characters or wrapped lines dropped a bracket—format and retype the affected accessors.
  • Symptom → Nested Record.Field/Table.ExpandTableColumn edits fail. Fix → Convert to temporary let-bindings; verify each part compiles before inlining.
  • Symptom → Multiple similar errors cascade. Fix → Fix from the top-most failing step; later errors often disappear once the first imbalance is corrected.

Validation & Next Steps

After balancing brackets, reformat (Ctrl+Shift+F), then step through the query to confirm each transformation previews correctly. For resilience, normalize headers once (trim/case/replace spaces) and prefer stable names. When referencing columns with dynamic or localized captions, consider Table.ColumnNames checks before access. Keep complex expressions small—introduce intermediate steps so bracket mismatches are easier to spot and fix.

Sources

Power Query M language specification

Record.Field

Table.TransformColumnNames

Labels

Tool/Power Query, OS/Cross-platform, Topic/M Syntax & Column References