Understanding Expression Syntax Errors in Power Query M

Expression Syntax Errors occur when there’s a mistake in how you have written an expression, such as missing parts or having elements in the wrong order. Understanding these errors is crucial for debugging and error-proofing your queries. This article explains the different types of Expression Syntax Errors, providing you with the knowledge to troubleshoot and resolve them.

What Are Expression Syntax Errors?

Expression Syntax Errors are mistakes in the structure of your Power Query code. They can occur for various reasons, such as misplaced commas, missing values, or unrecognized words. When Power Query encounters these errors, it stops the execution and displays an error message to help you identify the issue.

Defining A Token

Errors often occur because of an issue with a token. A token can be any of the following:

• Identifier: A name that refers to a value, like a variable, step or query name.
• Keyword: a reserved word that can’t be used as an identifier. Examples include: and as each else error false if in is let meta not null or otherwise section shared then true try type #binary #date #datetime #datetimezone #duration #infinity #nan #sections #shared #table #time.
• Literal: a direct representation of a value, such as a logical-literal, text-literal, null-literal etc.
• Operator and Punctuators: symbols used for operations or to separate elements, like: , ; = < <= > >= <> + - * / & ( ) [ ] { } @ ! ? ?? => .. ....

Types of Expression Syntax Errors

Let’s look at the different Expression Syntax Errors in the M language.

Expression.SyntaxError: Token Expected

This error, labelled as “Expression.SyntaxError: Token Expected,” occurs when Power Query expects a token but doesn’t find one. Let’s explore some variations.

Missing Identifier and Equal Sign
The code below lacks an identifier and an equal sign, causing an error.

let   
  Source = [ Name="Mary", Occupation="Dentist" ], 
  Source[Name] 
in 
  Result

To fix this, add an identifier and an equal sign:

let   
  Source = [ Name="Mary", Occupation="Dentist" ],
  Result = Source[Name] 
in 
  Result

Missing ‘in’ Token
If you leave out the ‘in’ token in a let..in statement, you’ll encounter the error labelled as “Expression.SyntaxError: Token ‘in’ expected”. For example, the code below is incomplete:

let 
  a = 5

To resolve this issue, include the ‘in’ token followed by the output expression, like so:

let 
  a = 5
in
  a

Missing Closing Parenthesis
When your code lacks a closing parenthesis, you’ll see an error message that reads “Expression.SyntaxError: Token ‘)’ expected.” For instance, the code below is missing a closing parenthesis:

List.Combine( { {1,2}, {3,4} } 

To correct this, simply add the missing closing parenthesis at the end of the expression:

List.Combine( { {1,2}, {3,4} } )

Expression.SyntaxError: A ‘x’ cannot precede a ‘y’

This error is related to misplaced characters in your code

Comma Without Subsequent Value
In Power Query, lists use curly braces and records use square brackets. Both are separate elements with commas. An error arises if a comma is not followed by a value or another element.

For example, the code below will trigger an error because a comma precedes a closing brace and the closing bracket:

// Expression.SyntaxError: A ',' cannot precede a '}'
{ 1, 3, }

//  Expression.SyntaxError: A ',' cannot precede a ']'.
[ a = 1, b = 2, ]

To fix these errors, remove the trailing commas:

{ 1, 3 }

[ a = 1, b = 2 ]

Trailing Comma in ‘let..in’ Expressions
In a let..in statement, variables are separated by commas. However, the last variable before the ‘in’ keyword should not have a trailing comma. If it does, you’ll encounter an error: “Expression.SyntaxError: A ‘,’ cannot precede a ‘in’.”

For example, the following code has a trailing comma that triggers this error:

// Expression.SyntaxError: A ',' cannot precede a 'in'.
let 
  Var1 = 1, 
  Var2 = Var1 + 2, 
in
  Var2

To resolve this issue, simply remove the trailing comma before the ‘in’ keyword:

// Output: 2
let 
  Var1 = 1, 
  Var2 = Var1 + 2
in
  Var2

Expression.SyntaxError: Token Literal expected

This error, labelled as “Expression.SyntaxError: Token Literal expected,” arises when Power Query anticipates a specific value or literal that is not provided.

Missing Parenthesis Before Comma

In the example below, the error suggests a literal is missing. In this case, there is both a missing closing parenthesis and a comma that suggests another variable will follow.

let 
  myvalue = List.Max( {1, 4, 6, 8 } , 
in 
  myvalue

We can fix this by removing the comma and closing the function with parenthesis.

let 
  myvalue = List.Max( {1, 4, 6, 8 } ) 
in 
  myvalue

Incomplete Operator

Operators like division or coalesce require values on both sides. Failing to provide them triggers this error. For instance, the following expressions are incomplete, causing the error: “Expression.SyntaxError: Token Literal expected”

5 *

"Text "??

To resolve these issues, supply the missing values:

5 * 5 // Returns 25

"My" ?? "Your" // Returns "My"

Expression.SyntaxError: The type identifier is invalid

This error, labelled as “Expression.SyntaxError: The type identifier is invalid,” occurs when you attempt to define a column with a type that Power Query doesn’t recognize.

For instance, the following code tries to assign a non-existent type, triggering the error:

Table.AddColumn( Source, "Custom", each "Rick", type nonexisting )

To resolve this issue, specify a recognized type. If you have a table named ‘Source,’ you can correctly add a new column with a text type like this:

Table.AddColumn( Source, "Custom", each "Rick", type text)

Expression.SyntaxError: Token Identifier expected

This error, labelled as “Expression.SyntaxError: Token Identifier expected,” happens when the first variable or step name is missing in the advanced editor.

For example, the following code lacks an identifier for the first step:

let 
  "MyText", 
  Result = Number.From( Source ) 
in 
  Result

To fix this, specify an identifier for the first step:

let 
  Source= "MyText",
  Result = Number.From( Source ) 
in 
  Result

Expression.SyntaxError: Token Eof expected

This error, labelled as “Expression.SyntaxError: Token Eof expected,” occurs when Power Query expects the end of the file but encounters incomplete or extra tokens.

Incomplete or Extra Tokens

For instance, the following examples contain either two numbers without an operator in between or an extra closing brace, both of which trigger the error:

true false

{ 1 , 2 } }

To resolve these issues, complete the expressions properly.

Errors in ‘if’ Statements

When writing an ‘if’ statement, omitting the ‘if’ keyword or using it in capital letters will result in an error. For example, both of the following lines will trigger errors:

5 > 4 then 5 else 6 

IF 5 then 10 else 6

Expression.SyntaxError: Invalid literal

This error, labelled as “Expression.SyntaxError: Invalid literal,” occurs when Power Query encounters a literal it doesn’t recognize.

For instance, the following code includes an invalid literal:

"z

Conclusion

Understanding Expression Syntax Errors in Power Query M is essential for effective debugging and writing robust queries. This article has provided a comprehensive guide to various types of syntax errors you might encounter, from missing tokens and misplaced characters to invalid literals and types.

By recognizing these common pitfalls, you can quickly troubleshoot and resolve issues, ensuring that your Power Query operations run smoothly.

Related Articles

• Error Messages in Power Query M