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 you are likely to encounter.
Table of Contents
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 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 message will change. Therefore, it is not worth looking at this message until we think the formula is finished. If the Show error link is visible, we can click it to take us to where the problem is.
Once you know what the messages mean, they are not as confusing as might initially seem. The most common warnings you’ll come across are:
- Token Literal expected means the next thing in the formula is expected to be a value, column name, or function.
- Token Then expected, or Token Else expected means the words then or else are expected to be entered. These will appear when writing an if statement.
- Token RightParen expected means that a closing bracket (or parentheses depending on your local vernacular), is expected to close a formula.
- A Comma cannot precede a RightParen means what it says; a comma cannot be directly in front of a closing bracket. There are no circumstances in M where this should be necessary.
- 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).
- Token EoF expected usually occurs when an invalid function name is used, or it uses the wrong case (for example, if is a valid command, while If with an upper case I is not).
- 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.
- The formula is incomplete usually indications no formula has been entered (only the equals symbol in the formula box).
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.
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. So we know it should 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?
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.
Where there are multiple errors in the code, we may need to go through several rounds of error 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.
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 correct 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 is not ideal, as it ignores the data privacy settings entirely. However, it’s a useful little fix if you are the only person accessing the data.
Click File > Option Settings > Query Options.
The Query Options window dialog box. 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 combine the queries into a single query. The most straightforward approach to achieve this is shown 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 specifically wrong with the data or the process, 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, but they just don’t fit 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.
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. 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.
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
Generally, Column header names are hardcoded somewhere within the M code. Therefore, any changes in source data structure can trigger the following 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. Queries and Connections pane shows the error and indicates the number of lines with errors.
The screenshot above shows 50 errors, but it could easily be just 1 or 2, depending on the structure of the data.
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. However, 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 color 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
- Introduction to Power Query
- Get data into Power Query – 5 common data sources
- Data Refresh Power Query in Excel: 4 ways & advanced options
- Use the Power Query editor to update queries
- Get to know Power Query Close & Load options
- Power Query Parameters: 3 methods
- Common Power Query transformations (50+ powerful transformations explained)
- Power Query Append: Quickly combine many queries into 1
- Get data from folder in Power Query: combine files quickly
- List files in a folder & subfolders with Power Query
- How to get data from the Current Workbook with Power Query
- How to unpivot in Excel using Power Query (3 ways)
- Power Query: Lookup value in another table with merge
- How to change source data location in Power Query (7 ways)
- Power Query formulas (how to use them and pitfalls to avoid)
- Power Query If statement: nested ifs & multiple conditions
- How to use Power Query Group By to summarize data
- How to use Power Query Custom Functions
- Power Query – Common Errors & How to Fix Them
- Power Query – Tips and Tricks
Discover how you can automate your work with our Excel courses and tools.
Excel Academy
The complete program for saving time by automating Excel.
Excel Automation Secrets
Discover the 7-step framework for automating Excel.
Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.