[Excel/Power Query] Error: Expression.Error — Token Literal Expected (syntax) — How to Fix It

Summary

Expression.Error: Token Literal Expected indicates a syntax problem in Power Query M. The parser reached a point where it expected a literal (text/number/list/record), but found something else—often a missing comma, quote, bracket, or a malformed anonymous function. Fix it by opening the Advanced Editor, locating the highlighted position, and correcting punctuation, quoting, or structure. Typical culprits: missing commas in records/lists, unquoted field names with spaces, = used instead of =>, and incomplete parentheses.

Context

M code is whitespace-tolerant but punctuation-sensitive. Constructs like records [Field="x", Other=1], lists {1,2,3}, and functions (x)=>x+1 require exact separators and balanced delimiters. Steps generated by the UI are valid; errors usually arise after manual edits, copy/paste from web snippets, or refactors that omit a comma or quote. The editor highlights the first unexpected token, which is often just after the real mistake. Formatting (Ctrl+Shift+F) re-indents code to reveal structure breaks.

Probable Cause

  • Missing/extra punctuation. Omitted commas between record fields or function arguments; unbalanced (), [], or {}.
  • Unquoted identifiers. Field/column names with spaces/special characters not quoted, e.g., [Total Amount] instead of [#"Total Amount"].
  • Wrong operator in anonymous functions. Using = instead of => (lambda arrow) after parameter lists.
  • Accidental newline inside literals. Line breaks inserted within strings, lists, or records without proper continuation.
  • JSON-like but not M syntax. Records require = and commas; braces alone or colons (:) are invalid in M records.

Quick Fix

  1. Open Advanced Editor. Home → Advanced Editor. Navigate to the step shown in the error; the cursor highlights the first unexpected token.
  2. Format code. Press Ctrl+Shift+F to reindent. Misplaced commas/parentheses become visually obvious.
  3. Check records/lists. Ensure commas between fields/items and use = for record fields.
// ❌ Missing comma between fields
[Customer="A" Amount=10]

// ✅ Correct
[Customer="A", Amount=10]

// ❌ JSON-style colon (invalid in M)
[Customer: "A", Amount: 10]
  1. Quote names with spaces. Use #"…" in field access or record keys.
// ❌ Unquoted space
[Total Amount]

// ✅ Quoted via #""
[#"Total Amount"]
  1. Fix anonymous functions. Parameter list then =>, not =.
// ❌ Wrong
(x) = x + 1

// ✅ Correct
(x) => x + 1
  1. Balance delimiters and arguments. Every (, [, { must close; every call needs parentheses.
// ❌ Missing closing )
DateTime.LocalNow(

// ✅ Correct
DateTime.LocalNow()

Full Example

Scenario 1: Missing comma in Table.AddColumn arguments

// ❌ Token Literal Expected near 'each'
Table.AddColumn(Source "Net", each [Amount] - [Tax], type number)

// ✅ Add missing comma between Source and column name
Table.AddColumn(Source, "Net", each [Amount] - [Tax], type number)

Scenario 2: Unquoted field with spaces

// ❌ Field access without quoting
Table.TransformColumns(Source, {{"Total Amount", each Number.From(_), type number}})
// ...later...
= [Total Amount] + [Tax]

// ✅ Use #"" for spaced names in record/row access
= [#"Total Amount"] + [Tax]

Scenario 3: Wrong lambda operator inside BYROW-like custom function

// ❌ Uses = instead of =>
Table.AddColumn(Source, "Flag", (r) = if r[Qty] > 0 then "OK" else "Zero")

// ✅ Correct lambda
Table.AddColumn(Source, "Flag", (r) => if r[Qty] > 0 then "OK" else "Zero")

Scenario 4: JSON-looking record in custom step

// ❌ JSON-style
Meta = { "Region": "EU", "Year": 2025 }

// ✅ M record with =
Meta = [ Region = "EU", Year = 2025 ]

Scenario 5: Incomplete let/in block or stray newline

// ❌ 'in' missing, parser expects literal
let
  Clean = Table.TransformColumns(Source, {{"Amount", each Text.Trim(_), type text}})
  
// ✅ Complete let/in
let
  Clean = Table.TransformColumns(Source, {{"Amount", each Text.Trim(_), type text}})
in
  Clean

Pitfalls & Debug

  • Symptom → Error points to each. Fix → Check the argument separator just before it; a missing comma often misattributes the error to each.
  • Symptom → “at end of input”. Fix → A closing ), ], or } is missing; count delimiters or format to reveal imbalance.
  • Symptom → Column name with punctuation. Fix → Always use [#"Exact Header"] when accessing record fields with spaces/slashes/dots.
  • Symptom → Edited UI-generated step now fails. Fix → Compare with a fresh equivalent step from the UI to spot punctuation differences.
  • Symptom → Multi-line string broken. Fix → Strings cannot span lines without concatenation; keep on one line or build with Text.Combine.

Validation & Next Steps

After fixes, validate structure and execution:

// Reformat to surface structure
// Shortcut: Ctrl+Shift+F (Advanced Editor)

Run step-by-step from top to the failing step. If editing complex expressions, decompose them: assign intermediate variables in a let block, then rebuild. Prefer UI-generated steps for common transforms and only hand-edit small, reviewed changes. For collaboration, keep a working copy of the query to diff against when syntax issues arise.

Sources

Power Query M language specification

Power Query M function reference

Advanced Editor & formatting

Labels

Tool/Power Query, OS/Cross-platform, Topic/M Syntax & Expression Errors