[Excel/Power Query] Error: Expression.Error — Token RightParen Expected (missing “)”)

Summary

Expression.Error: Token RightParen Expected means Power Query’s M parser found an opening parenthesis ( without a matching closing ). It typically arises in nested function calls, after edits that add/remove arguments, or when a comma/comment interrupts closure. The fix is mechanical: open the Advanced Editor, format the code (Ctrl+Shift+F), then balance every ( with a ). Pay special attention to multi-line calls like Table.AddColumn and lambdas with each.

Context

M code is punctuation-driven. Function calls require balanced parentheses; records use [ ], lists use { }. When a closing token is missing, the parser keeps expecting more input until the end of the step, then throws “RightParen Expected.” The highlighted position in the editor is usually where the parser first noticed the mismatch, which may be just after the actual omission. Common triggers include refactoring arguments, splitting a long call across lines, or copy/pasting snippets that removed the last ). Formatting reveals the block structure and helps pinpoint the missing closure.

Probable Cause

Missing closing parenthesis in a function call. e.g., Text.Upper([Name] without ).
Nesting not closed. Multi-level expressions like Table.AddColumn(..., each Text.Upper(Text.Trim([Name])) missing the final ).
Misplaced commas or extra arguments. A stray comma suggests another argument should follow, causing the parser to wait for a ) that never comes.
Manual edits removed/shifted a ). Especially after adding type … annotations.
Line breaks/comments in the middle of a call. An inline comment after a comma can hide the need for a closing token.

Quick Fix

Open Advanced Editor. Home → Advanced Editor. The error line is typically near the end of the step or the query.
Format the code. Press Ctrl+Shift+F to reindent. Unbalanced blocks become visually obvious.
Balance parentheses from inner to outer calls. Count each ( and ensure a matching ) before closing the outer function.

// ❌ Missing final ) in nested calls
Table.AddColumn(Source, "UpperName", each Text.Upper(Text.Trim([Name]))

// ✅ Close inner and outer calls
Table.AddColumn(Source, "UpperName", each Text.Upper(Text.Trim([Name])))

Confirm argument separators. Each argument is separated by a comma; no trailing comma before the closing ).

// ❌ Trailing comma before )
Number.Round([Amount], 2, RoundingMode.AwayFromZero, )

// ✅ No trailing comma
Number.Round([Amount], 2, RoundingMode.AwayFromZero)

Review long multi-line calls. Ensure the last line ends with a closing ) for the function and the step’s overall expression.

Full Example

Scenario 1: Nested Table.AddColumn with omitted closure

// ❌ Error: Token RightParen Expected near 'each'
let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  WithFlag = Table.AddColumn(
      Source,
      "Flag",
      each if Text.StartsWith(Text.Upper([Category]), "A") then 1 else 0
in
  WithFlag

Fix: Close the Table.AddColumn call and complete the let … in block.

// ✅ Balanced parentheses and complete block
let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  WithFlag = Table.AddColumn(
      Source,
      "Flag",
      each if Text.StartsWith(Text.Upper([Category]), "A") then 1 else 0
  )
in
  WithFlag

Scenario 2: Function chain with stray comma

// ❌ Trailing comma leads parser to expect another argument + ')'
let
  Rounded = Table.TransformColumns(Source, {
      {"Amount", each Number.Round(_, 2, RoundingMode.AwayFromZero, )}
  })
in
  Rounded

Fix: Remove the trailing comma.

// ✅ Correct argument list
let
  Rounded = Table.TransformColumns(Source, {
      {"Amount", each Number.Round(_, 2, RoundingMode.AwayFromZero)}
  })
in
  Rounded

Scenario 3: Multi-line lambda missing parenthesis

// ❌ Missing final ) after 'each' body
let
  Added = Table.AddColumn(Source, "Net", each
      let
          gross = Number.From([Price]),
          tax   = Number.From([Tax])
      in
          gross - tax
in
  Added

Fix: Close Table.AddColumn(...) and the query.

// ✅ Add the missing ')'
let
  Added = Table.AddColumn(Source, "Net", each
      let
          gross = Number.From([Price]),
          tax   = Number.From([Tax])
      in
          gross - tax
  )
in
  Added

Scenario 4: Inline comments disrupting closure

// ❌ Comment after comma obscures missing ')'
let
  C = Table.SelectRows(Source, each [Qty] > 0, // keep positives
in
  C

Fix: Move the comment or finish the call first.

// ✅ Close call, then comment
let
  C = Table.SelectRows(Source, each [Qty] > 0) // keep positives
in
  C

Pitfalls & Debug

Symptom → Error highlights each. Fix → The real issue is usually the missing ) at the end of the containing function (e.g., Table.AddColumn); add it after the lambda body.
Symptom → Error “at end of input.” Fix → A closing ) is missing in the final lines; format and inspect the last 3–5 lines.
Symptom → Advanced Editor won’t save. Fix → Temporarily comment recent edits with //, then reintroduce them while ensuring each opening token is closed.
Symptom → Long argument lists. Fix → Put each argument on its own line and ensure the final line closes with ).
Symptom → Mixed record/list and function calls. Fix → Balance [] and {} first, then (); unbalanced inner literals mislead the outer call’s closure.

Validation & Next Steps

After fixing parentheses, validate structure deterministically:

1) Ctrl + Shift + F   # format and reindent
2) Ensure each multi-line call ends with ')'
3) Refresh Preview    # confirm the step runs without syntax errors
4) For complex steps, extract inner logic to a 'let ... in' sub-block, then call it

Adopt a defensive style: keep function calls short, prefer UI-generated steps for boilerplate, and add comments at the end of lines (after closures). For nested transforms, close inner functions immediately and only then add more arguments to the outer call.

Sources

Power Query M language specification

Advanced Editor in Power Query

Table.AddColumn

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

Search for an Error

Error Atlas