Common Power Query errors & how to fix them

I’m sure you’ve not got this far without encountering your fair share of Power Query errors. Just like Excel and other applications, Power Query has its own unique error messages. You’ve probably forgotten the first time you encountered the #NAME? or #VALUE! errors in Excel, but over time you hopefully worked out what to do when they arose. Now you are seeing Power Query errors, which probably appear strange and unfamiliar. It can be daunting at first, but over time you will understand what the errors are and what causes them.

While we can’t cover every error, the purpose of this post is to help demystify some of the more common errors messages you are likely to encounter as a beginner/intermediate Power Query user.

A key thing to remember is that error messages are not bad. They are a good thing; it is Power Query’s way of telling us that it doesn’t understand what we are asking it to do.

Table of Contents

Types of Power Query errors
Process creation errors

M code errors
Formula.Firewall error

Data processing errors

Wrong source location
Missing column names
Incorrect data types

Software bugs
Conclusion

Types of Power Query errors

Error messages can appear in various places, such as in the Queries & Connections pane, within the Power Query Editor, or maybe just as a value in a field.

I have grouped the common errors into three types:

Process creation errors
Data processing errors
Software bugs

We look at each of these and find out how to fix the most common issues.

Process creation errors

Process creation errors occur as we build a query. These are driven by either errors in the M code, or our lack of understanding of how Power Query works.

M code errors

M code errors can be challenging to find, especially if we are new to the language. A comma, a mistyped word, or even a capital letter is enough to cause the process to fail. The three main places where we can edit M code are:

Custom Columns
Advanced Editor
Formula Bar

Let’s start by looking at Custom Columns, then move on to look at the Advanced Editor and Formula Bar.

Custom Columns

Of the M coding options, the Custom Column feature is the most accessible and the one we are most likely to use.

Custom Columns contain a syntax check at the bottom of the screen to help guide us with formulas. Unfortunately, unless we’ve been working with Power Query for a while, we won’t necessarily understand what many of these messages mean.

The screenshot above shows the Token RightParen expected error message (we can also see a red squiggly underline below the comma). This is just one of many potential messages.

As we type into the formula box, the error message reflects the current state of the code. Therefore, it is not worth looking at this message until we think the formula is finished. If the Show error link is visible, clicking it will show us where the problem is.

Once you know what the messages mean, they are not as confusing as might initially seem.

Many error messages refer to a Token, but what is a Token? A token can be:

Literal – something that represents a value (examples include: numbers, text, null, formula result, column value)
Identifier – a variable, step name or query name
Keyword – words which are reserved for special purposes (examples include: if, then, else, true, false, in, let, each, null, not)
Operators – special symbols used within the code – (examples include: + - * / [ ] { } ( ) < > <= >= = <>)

The most common Token warnings we encounter are:

Token Literal expected means the next thing in the formula is expected to be a value, column name, or function.
Token ‘)’ expected means the next character expected by Power Query is a closing bracket.
Token ‘,’ expected means the next character expected by Power Query is a comma.
Token ‘then’ expected means when writing an if statement, Power Query is expecting the keyword then to be used.
Token ‘else’ expected means the word else is expected to be entered while entering an if statement.
Token RightParen expected means that a closing bracket (or parentheses depending on your local vernacular), is expected to close a formula.
Token Eof expected occurs where there are characters that Power Query is not expecting. Examples include: (1+1)), or encountering a then without an if.
Token internal expected means the logical test, true value, or false value of an if statement is missing, or a formula contained within these arguments is incomplete.
Token ‘in’ expected means we have used the ‘let’ keyword, which requires a closing ‘in’ keyword to be complete.
Invalid literal indicates an issue with the value entered as an argument (this often occurs when a text string has not been closed using the double quotation character).
A ‘x’ cannot precede a ‘y’ can come in many forms. This is to indicate where we have not followed the correct syntax. A common example is: A',' cannot preceded a 'in'. We just need to correct our code as described.
The formula is incomplete usually indicates no formula has been entered (only the equals symbol in the formula box).

In the Custom Column dialog box, we cannot enter the formula until all errors are resolved.

Once we get the message that No syntax errors have been detected, we can click the OK button to close the window. Of course, this doesn’t mean the formula or data types are correct, but the syntax has been entered correctly.

Advanced Editor & Formula Bar

The Advanced Editor and Formula bar accept changes even if it causes an error. Unfortunately, this means the variety of error messages increases when using these features:

The Advanced Editor has the same warning message at the bottom as a Custom Column but allows us to click Done even if there is an error in the code.
The Formula Bar has no error checks. We can make any changes to the code and press the Enter key to accept those changes without any checks.

Given the multitude of possible errors we could create, we can’t go through all of them. However, it is much easier to troubleshoot once you know how to read the error message.

Advanced Editor syntax errors

Where there are syntax errors in the Advanced Editor, it highlights them with a red squiggly underline and describes the error at the bottom.

Advanced Editor syntax error - Power Query errors

In the example above, the comma is missing at the end of the Source step. Therefore, this creates an error at the start of the #”Changed Type” step.

The underline may not show us exactly where the issue is; however it highlights at what point Power Query identifies the error. Therefore, we know it must be in the code prior to the error.

Expression syntax errors

As noted above, nothing stops us from entering errors into the Advanced Editor or Formula Bar.

The screenshot below shows an Expression.SyntaxError… hmmm… what does that mean?

Expression.Syntax just means there is an error in the code, and then a description of the error.

If we look below the error message, Power Query has kindly shown us where the error is. If you notice, there is an arrow —->; this indicates the line that contains the error. By looking along that line, we find a group of ^^^; these pinpoint where the error resides.

In our example above, the error is that we have used a data type of tet, which is invalid, it should be text.

Where there are multiple errors in the code, we may need to go through several rounds of bug fixing as the error message will only show one error at a time.

Formula.Firewall error

There is a very frustrating error, which will rear its head from time to time – the dreaded Formula.Firewall error.

This error can take two forms:

Error message #1

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

Error message #2

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

Formula.Firewall error from combining data sources

What do these mean? And how can we fix it?

Power Query does not like to use two data sources with different privacy settings. This usually occurs when there are:

External and internal data sources combined in a single query
Dynamic data sources used to define the source of another query

The following steps should fix the Formula.Firewall error.

Apply privacy settings

Let’s start by applying the privacy settings. We can do this by ignoring privacy or using the correct setting for each data source.

Ignore privacy

This first option ignores the data privacy settings entirely. This may sound risky, however unless you are dealing with data sources which send data (e.g. a SQL Query) then there is no data shared.

Click File > Option Settings > Query Options.

The Query Options window dialog box appears. Select Privacy > Always ignore Privacy Level settings, then click OK.

Apply privacy for each data source

Alternatively, rather than ignoring the privacy settings, we could set them correctly.

To set the data source for inputs, click File > Options > Data source settings.

In the data source settings dialog box, select the source and click edit permissions. This allows us to set the privacy setting for each source.

There are four privacy settings:

None: There are no privacy settings applied. Microsoft recommends only using this in a controlled environment.
Private: The data is confidential or sensitive and should not be shared. This data cannot be shared with another data source.
Organizational: The data can be shared within the organization. This data can only be shared with other organization data sources.
Public: The data can be shared with any other data source, including public or organizational sources.

We should set the correct privacy level for our data sources.

Flattening queries

If there is still a Formula.Firewall error, we can flatten the queries.

To find out more but this approach check out this post: How to fix the Formula.Firewall error in Power Query (2 ways)

The most straightforward approach to avoid the Formula.Firewall error is in this post: https://exceloffthegrid.com/power-query-source-cell-value/

Data processing errors

Data processing errors occur when the data is fed through the transformation process.

There may be nothing wrong with the data or the process individually, yet the two don’t work well together. It could be something as simple as the transformation steps expecting to find a column called “Product”, but a “Product” column does not exist in the data set. Neither the data nor the process is incorrect, they just don’t work together.

The most common errors in this area are:

Wrong source location
Column name changes
Incorrect data types

Let’s look at each of them in a bit more detail

Wrong source location

The wrong source location error occurs when a file or database changes location, or a server has crashed, and therefore the source cannot be accessed. Either way, Power Query can’t find the source data, which leads to the [DataSource.Error] error.

After refreshing, an error message like the following will appear, detailing the file location it cannot find.

We also see an error in the Queries & Connections window.

Download did not complete - file not found

If we double-click the query, we find out more detail about the error. The Power Query editor opens and shows the following message. Click Go To Error to go to the exact step.

Error within the Power Query Editor source missing

Finally, we can click Edit Settings to change the source location in the window.

There are other, and maybe better, options for changing the source data location; I have written about this in a previous post, so check out that for more details.

Missing column names

Often, column names are hardcoded somewhere within the M code. Therefore, any changes in source data structure can trigger the following error.

Missing column names might lead to the [Expression.Error] error.

The Queries & Connections pane will show the same Download did not complete error we saw earlier. Opening the Query reveals further details about the error.

Ideally, we should aim to build queries that can be flexible when column names change, though that isn’t always possible.

As a quick fix, we can either:

Change the header name in the source data
Correct the hard-coded value in the M code through the Advanced Editor or Formula Bar
Delete the old step and insert a new one that correctly picks up the new column name.

But you must be careful; poorly implemented changes can cause other problems further down in the query.

Incorrect data types

Data type errors will not prevent the data from loading into the query; instead, those cells are loaded as blank. The Queries and Connections pane shows the error and indicates the number of lines with errors.

Queries & Connections Pane showing errors

The screenshot above shows 50 errors, but it could be the same error affecting all the rows.

Data type errors occur when:

Data is converted from one type to another – for example, trying to change a text string into a decimal data type
Incorrect data types used within functions – for example, trying to use a number function on a text data type, or trying to multiply text values

Excel is very forgiving and will happily switch between data types where it can. Power Query is not as forgiving; therefore, getting the correct data type is essential.

After opening the query, Power Query shows the errors. The pink colored bar below the column header displays the % of errors found in the first 1000 records.

If the error is not found within the first 1000 records:

Change the setting in the status bar to column profiling based on the entire data set.
Filter to include only errors by clicking Home > Keep Rows > Keep Errors

After clicking the word “Error” within the Preview Window, it provides details about the specific issue.

In the screenshot above, we can see that Power Query was trying to convert a text value into a date, which caused the error.

While there may be multiple lines with errors, it does not mean you must fix each row individually. Changing one step may be enough to fix all the errors at the same time.

Software bugs

Finally, there is another unfortunate type of error that is outside of our control; software bugs.

When I started using Power Query, I came across two issues (though I didn’t know they were bugs at the time). In both cases, I concluded it was my fault for not understanding the tool correctly. However, it wasn’t me, but the software which was not working correctly.

As Power Query is continually updated, bugs can come and go quickly as newer versions are released. However, I would say that over the past few years, Power Querty has become robust and rarely suffers from issues.

Hopefully, you will not encounter any of the problems I had; they have already been resolved. Therefore, if you meet an issue where the software is not behaving as documented, then updating to the newest version should resolve the issue. Also, ensure you report any issues to Microsoft; they can only fix issues if they know they exist.

Conclusion

Power Query error messages can seem confusing as they use terms that we are unfamiliar with. However, I hope this post has helped you to identify your error and provides suggestions on how to fix it.

If you really get to grips with the M code aspect of error messages, check out the error messages page on powerquery.how.

Read more posts in this Introduction to Power Query series