Error Messages

Updated on
Rick de Groot

Error messages in Power Query M are notorious for being cryptic and leaving you behind wondering what went wrong. This article addresses the most common error messages in Power Query. It shows what causes the errors and what you can do to fix them.

Table of contents

Expression Syntax Errors

When an expression misses an important element, it can cause an Expression Syntax Error. This error means there’s a mistake in how you’ve written the expression, like missing parts or having the wrong order. The error can be caused by misplaced commas, missing values, or unrecognized words. let’s delve into how these errors can occur.

Expression.SyntaxError: Token Expected

A “Token Expected” error happens when Power Query is looking for a specific element, like an operator or a function parameter, but doesn’t find it.

Example 1: Missing Closing Parenthesis

Consider the Table.AddColumn function below. It lacks a closing parenthesis, causing the error “Expression.SyntaxError: Token ‘)’ expected.”

/* "Expression.SyntaxError: Token ')' expected"
   The expression misses a closing parenthesis */
Table.AddColumn(#"Converted to Table", "Custom", each "abc"

You can resolve the error by adding a closing parenthesis at the end.

Example 2: Missing Identifier and Equal Sign

In this example, the error message is “Token ‘=’ expected.” The code lacks both the identifier and the equal sign before the Record.ToTable line.

/* Expression.SyntaxError: Token '=' expected. */
let
    Source = [Name="John", Age=30, Occupation="Engineer" ],
    Record.ToTable(Source)
in
    #"Converted to Table"

Fix the expression error by adding a variable name and an equal sign as prefix to the expression on line 3.

Example 3: Missing ‘in’ Token

Another frequent mistake is omitting the ‘in’ token in a let..in statement. The error message will be “Expression.SyntaxError: Token ‘in’ expected.”

/* Expression.SyntaxError: Token 'in' expected 
   The example opens a second let statement, but does not close it */
let
  source = let
in
  source

Example 4: Unclosed String and Missing Comma

The field value ‘Engineer’ is not properly closed. This makes it seem as if the value runs up till the next line’s quotations. This part however does not follow with a comma, which is required for the let..in statement.

/* Expression.SyntaxError: Token ',' expected. 
   Line 2 does not properly close the string with quotations */
let
    Source = [Name="John", Age=30, Occupation="Engineer],
    #"Converted to Table" = Record.ToTable(Source)
in
    #"Converted to Table"

Expression.SyntaxError: A ‘,’ cannot precede a ‘}’

Comma Without a Following Value in Lists

In Power Query, lists are made up of values separated by commas and enclosed in curly braces. An error occurs if a comma is present but not followed by a value. For example, in the list {1,3,}, the trailing comma implies that another value should follow. Since no value is provided, an error is triggered.

// The last value in a list should not be followed by a comma
{1,3,}

Trailing Comma in ‘let..in’ Expressions

The ‘let..in’ expression also requires variables to be separated by commas. However, the last variable before the ‘in’ keyword should not be followed by a comma. Doing so will result in an error message stating, “A ‘,’ cannot precede a ‘in’.”

/* Expression.SyntaxError: A ',' cannot precede a 'in'.
   The comma at the end of the 'result' line, suggests another variable is coming */
let
    Source = [Name="John", Age=30, Occupation="Engineer" ],
    Result =  Record.ToTable(Source),
in
    Result

Expression.SyntaxError: Token Literal expected

Missing Variable After Comma

In Power Query, some structures expect a specific value or literal to follow a certain token. In the first example, a comma appears before the ‘in’ keyword, suggesting that another variable should follow. Since no variable is provided, an error occurs.

// the comma before 'in' suggest another variable is coming
let myvalue = List.Max( {1, 4, 6, 8 }, in myvalue

Incomplete Operators

Operators usually require operands on both sides. Failing to provide them results in the same “Token Literal expected” error. For example:

// The division operator expects a value on the right and left side. 
5 / 

// The coalesce operator also expects an operand on both sides
5 ?? 

// A try..otherwise statement requires a value following the 'otherwise' literal
try 5 > 4 otherwise

Each of these examples triggers the error “Expression.SyntaxError: Token Literal expected.” To avoid this, ensure that your code is complete and that each token is followed by the appropriate value or literal.

Expression.SyntaxError: The type identifier is invalid

In Power Query, defining a data type requires using a recognized identifier. The following example tries to define a column with an unrecognized type, ‘oops,’ leading to an error.

// There is no 'type oops' in the M language
Table.AddColumn( Source, "Custom", each type oops)

This results in the error message: “Expression.SyntaxError: The type identifier is invalid.”

Expression.SyntaxError: Token Identifier expected

Errors can also arise when the first step name or variable is not specified in the advanced editor.

// First line after 'let' does not specify a identifier as variable name
let
    [Name="John", Age=30, Occupation="Engineer" ],
    Result =  Record.ToTable(Source)
in
    Result

This triggers the error: “Expression.SyntaxError: Token Identifier expected.”

Expression.SyntaxError: Token Eof expected

Incomplete or Extra Tokens

Power Query expects all expressions to be complete and correctly formatted. If an expression is incomplete, has extra tokens, or is capitalized incorrectly, an error is generated.

/* Expression.SyntaxError: Token Eof expected
   Lacks an operator between the numbers */
5 5

/* Expression.SyntaxError: Token Eof expected
   Has additional closing parenthesis */
(5 +5 ))

Both examples result in the error: “Expression.SyntaxError: Token Eof expected.”

Errors in ‘if’ Statements

The ‘Token Eof expected’ error is also common when there are mistakes in ‘if’ statements.

// Expression misses an opening 'if' clause
5 > 4 then 5 else 6

// If statements should be in lowercase
IF  5 then 10 else 6

Missing Tokens in Control Structures

Sometimes, the error message will specify which token is missing.

/* Expression.SyntaxError: Token 'else' expected.
   Expects an 'else' clause for the if..then..else control structure */
if 5 > 4 then 6

/* Expression.SyntaxError: Token 'then' expected.
   Expects an 'then' clause for the if..then..else control structure */
if 5 > 4 else 6

The first example triggers the error “Expression.SyntaxError: Token ‘else’ expected,” and the second one results in “Expression.SyntaxError: Token ‘then’ expected.”

Expression.SyntaxError: Invalid literal

Literals in Power Query are values like numbers or strings. The error emerges when the provided literal isn’t recognized by the language. In the example "a[1], the literal appears to be the start of a text string but isn’t completed, leading to the error.

// Literal appears as if a string, but doesn't close the definition
"a[1]

This results in the error message: “Expression.SyntaxError: Invalid literal.”

Expression Errors

Expression errors occur when there’s an issue with how you’ve structured or used expressions in Power Query. These errors can vary widely, from unrecognized names to data type mismatches to invalid operations. Below are some specific types of expression errors to help you understand what might be going wrong and how to fix them.

Expression.Error: The name ‘a’ wasn’t recognized. Make sure it’s spelled correctly

Unrecognized Name Error

The error “The name ‘a’ wasn’t recognized” happens when ‘a’ is not defined in any capacity—not as a variable, step, or query name. It also isn’t enclosed in quotes, which would identify it as a text value. If ‘a’ is supposed to be a library function, a misspelling could also trigger this error.

// Expression only works if 'a' was defined somewhere, else it returns an error
a + 1

Invalid Function Name Error

The same type of error can occur if you use a function name that doesn’t exist in the M language.

/* Expression.Error: The name 'Record.From' wasn't recognized.  Make sure it's spelled correctly.
   The Record.From function does not exist in the M language */
Record.From( 5 )

Both examples result in the error message: “Expression.Error: The name wasn’t recognized. Make sure it’s spelled correctly.”

To avoid these errors, double-check that all names and functions in your code are correctly spelt and properly defined.

Expression.Error: We cannot apply operator – to types Number and Date

Operators in Power Query work with specific types of values. For example, you can’t subtract a date from a number. Doing so results in a type mismatch error.

// The M language does not support subtacting a date value from a number value.
5 – #date( 2023,1,1)

Expression.Error: There weren’t enough elements in the enumeration to complete the operation

When you try to access elements in a list by their index, the list must have enough elements to match the index number. Otherwise, an error occurs.

/* Expression.Error: There weren't enough elements in the enumeration to complete the
   operation. Attempting to access the fourth item in a two-item list results in the error. */
{ "a", "b" }{3}

Expression.Error: The field ‘Col3’ of the record wasn’t found

To access fields in a record, the field must actually exist. If it doesn’t, you’ll get an error.

/* Expression.Error: The field 'Col3' of the record wasn't found.
   Field 'Col3' does not exist in the record */
[ Col1 = "a", Col2 = "b" ] [Col3]

Expression.Error: The column ‘C3’ of the table wasn’t found

When you attempt to select a column that has been removed from a table, an error is generated stating that the column wasn’t found.

/* Expression.Error: The column 'C3' of the table wasn't found.
   Column 'C3' does not exist in the table */
let
  myTable = #table( { "C1","C2" }, { { 1, "A" },{ 2, "B" } } ),
  pickCol = Table.SelectColumns( myTable, { "C3" } )
in
  pickCol

The same error occurs if you try to retrieve a non-existent column name using field selection.

/* Expression.Error: The column 'C3' of the table wasn't found.
   The table does not have a column named 'C3' */
#table( { "C1","C2" }, { { 1, "A" },{ 2, "B" } } ) [C3]

Expression.Error: The key didn’t match any rows in the table

When filtering a table by a key, the key should match exactly one row. If no row matches, an error is returned. Below examples searches for the value 3 in the column C1, which it doesn’t find.

/* Expression.Error: The key didn't match any rows in the table. 
   There is no row where Column 'C1' has a value of 3 */
#table( { "C1","C2" }, { { 1, "A" },{ 2, "B" } } ){ [C1=3] }

Expression.Error: The key matched more than one row in the table

Similarly, if a key matches more than one row, an error is generated. In the below example, the key matches multiple rows in the table, resulting in an error.

/* Expression.Error: The key matched more than one row in the table.
   There are multipe rows where Column 'C1' has a value of 1. */
#table( { "C1","C2" }, { { 1, "A" },{ 1, "B" } } ){ [C1=1] }

Expression.Error: We cannot apply field access to the type Number

Field access is valid for records and tables but not for numbers. Attempting to use field access on a number results in an error.

/* Expression.Error: We cannot apply field access to the type Number.
   Field access is only relevant for records and tables. It doesn't work for numbers. */
5[1]

Expression.Error: Evaluation resulted in a stack overflow and cannot continue

This recursive expression continues infinitely and causes a stack overflow. You should define a condition for the operation to end.

/* Expression.Error: Evaluation resulted in a stack overflow and cannot continue.
   The expression infinitely recurses without an end condition. */
let Add = (x) => @Add (x * 1) in Add(2)

Expression.Error: Invalid binary encoding

This error suggests improper encoding when converting a value to binary. In the below example, the text “Abc” isn’t a valid binary representation.

// ABC is not a valid binary representation.
Binary.From( "Abc" )

Expression.Error: We couldn’t parse the Duration literal

This error indicates an invalid format for duration literals. The Duration.From function expects a specific format when converting a text to a duration, namely d.h:m:s.

/* Expression.Error: We couldn't parse the Duration literal.
   The text value is not in the format "d.h:m:s" */
Duration.From( "Abc" )

Expression.Error: 1 arguments were passed to a function which expects 2

Functions in Power Query expect a defined number of arguments. In the below example, the function expects two arguments, but only one is provided, leading to the error.

/* Expression.Error: 1 arguments were passed to a function which expects between 2 and 3.
   The Value.Multiply function expects 2 arguments. */
Value.Multiply( 5 )

Expression.Error: We cannot convert the value 5 to type List

Certain operations are specific to data types. “Item Selection” is relevant to lists, while “Field Selection” and “Field Projection” are applicable to records and tables. Incorrectly using these operations will yield an expression error.

// Item selection operator {0} expect a list, but 5 cannot be converted to a list.
5{0}

/* Field selection and field projection expect a record or table, but 5 cannot
  be converted to type record */ 
5[[5],[6]]

Expression.Error: We cannot convert a value of type Binary to type Table

Certain operations are specific to data types. The Table.Combine operation expects a list of tables. In the example, Var1 is not of type table.

// Table.Combine accepts a list of tables, but also receives a value of binary format.
let 
  Var1 = Binary.From( "1011" ),
  Var2 = #table( { "C1","C2" }, { { 1, "A" },{ 2, "B" } } ),
  Combine = Table.Combine( { Var1, Var2 } )
in
  Combine

Expression.Error: A cyclic reference was encountered during evaluation

This error arises when there’s a loop in the reference chain, where a value or function refers back to itself either directly or indirectly.

// There's a circular dependency where the variables depend on each other
let
    Var1 = Var3,
    Var2 = Var1 + 10,
    Var3 = Var2
in
    Var3

DataFormat Errors

DataFormat errors in Power Query arise when there’s a mismatch between the expected data format and the provided data. These errors are typically a result of attempting to convert, parse, or process data in a way that’s incompatible with its current format. Understanding and addressing these errors often involves ensuring that the data being imported aligns with the expected formats and types in Power Query.

DataFormat.Error: We couldn’t parse the input provided as a Date value

This error arises when Power Query attempts to convert a value into a date format but fails because the provided input doesn’t match any recognizable date pattern. In the expression, the string “Abc” isn’t in a valid date format, which causes Power Query to throw the error.

// "Abc" is not a valid date format
Date.From( "Abc" )

DataFormat.Error: We couldn’t convert to Number

This error indicates that Power Query tried to convert a given input into a number, but the input wasn’t in a valid numerical format. In the example, the string “Abc” doesn’t represent a number, leading to the conversion error.

// "Abc" is not a valid number format
Number.From( "Abc" )

DataFormat.Error: Invalid cell value ‘#DIV/0!’

When you import data from Excel into Power Query, the system expects standard cell values. Error-values in Excel cells will trigger a “DataFormat.Error” because they are not standard data values; they indicate issues in the source data.

Here’s what these errors look like in an excel table.

Importing Error Values from Excel to Power Query

You would get these errors for the following reasons:

  • Invalid cell value ‘#DIV/0!’: This error occurs when a formula tries to divide a number by zero.
  • Invalid cell value ‘#REF’: This typically appears when a formula references a cell that has been deleted.
  • Invalid cell value ‘#VALUE’: This happens when a formula expects a certain data type but receives a different one. For example, attempting to subtract text from a number will trigger this error.
  • Invalid cell value ‘#NUM’: This error arises when a function produces a result that’s either too large or too small to be represented, or when the input doesn’t meet the function’s requirements. For instance, using DATEDIFF with a later date as the start date and an earlier date as the end date would cause this error.
  • Invalid cell value ‘#NA’: This occurs when a lookup function, like VLOOKUP, fails to find a matching value.

To avoid these errors, ensure that your Excel data is clean and free of these error values before importing it into Power Query.

DataFormat.Error: We reached the end of the buffer

When a Json.Document receives an empty string, it returns a data format error. This can also happen if your data source is too large, causing memory issues.

// Returns the error: we reached the end of the buffer.
Json.Document("")

To resolve this, ensure the function gets valid input and consider limiting the number of rows or columns in your data source to reduce memory usage.

Dataformat.Error: External table is not in the expected format

Power Query expects data to be in a specific format for certain functions. If the received file type doesn’t match what’s expected, an error is generated. For example, the Excel.Workbook function expects an XLS(X) file but receives a CSV file in the following case:

/* Dataformat.Error: External table is not in the expected format
   The function expects an xlsx file but receives a csv */l
Excel.Workbook(File.Contents("C:\Data\Avocado Prices.csv"), null, true )

To avoid this error, ensure that the file types you are working with match the expectations of the Power Query functions you are using.

Datasource Errors

Datasource errors in Power Query emerge when there’s an issue accessing or locating the data source you’ve specified. These errors often relate to issues with file paths, file accessibility, or the existence of the specified file.

DataSource.Error: Could not find a part of the path

This error arises when Power Query is instructed to access a file at a specific path but can’t find it, either because the path is incorrect, the file doesn’t exist, or there’s a typo in the path. In the example, Power Query tries to access the Excel file at the given path but can’t find it, resulting in the error.

// Below example gives an error in case the file path is incorrect. 
Excel.Workbook( File.Contents( "C:\Data\file.xlsx"), null, true)

DataSource.Error: Could not find file

Similar to the previous error, this one occurs when Power Query can’t locate the specified file. Common reasons include the file being moved, deleted, renamed, or the path being inaccessible due to permission issues.

// Below example only gives an error in case the current user can't access the path. 
Excel.Workbook( File.Contents( "C:\Data\file.xlsx"), null, true)

To avoid these errors, double-check your file paths and ensure that the files you’re trying to access actually exist and are accessible.

Formula Firewall

Formula Firewall errors in Power Query safeguard data privacy and security. They arise when data combinations could compromise confidentiality or integrity. To resolve these, you may need to restructure queries or modify privacy settings to ensure safe data operations.

Formula.Firewall: Query ‘Query1’ (step ‘Source’) is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination. 

This error is raised when a Power Query tries to combine data from sources with different privacy levels. Power Query has privacy levels (Public, Organizational, Private) to protect sensitive data. When data from sources with conflicting privacy levels is combined, the firewall prevents potential data exposure.

There is a risk when combining data from different sources that have incompatible privacy settings. As a result, Power Query’s firewall prevents the operation, signalling the need to reconsider the data combination.

Formula.Firewall: Query ‘Query1’ (step ‘Source’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. 

Power Query may raise this error when a query, instead of directly accessing a data source, references other intermediate queries or steps. This indirect referencing can be problematic, especially when considering data privacy and protection. Power Query’s firewall can identify this indirect access as a potential issue and flag it with an error.

Learn more about Error Messages in the following articles:

Contribute » | Contributors: Rick de Groot

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy