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 it’s 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 arise. Now that you’re working with Power Query, the error messages appear strange and unfamiliar and can be daunting at first. Over time you will start to 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.
Types of errors
Errors messages can appear in a variety of 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 errors into three types:
- Process creation errors
- Data processing errors
- Software bugs
We will look at each of these in turn and find out how to fix common issues.
Process creation errors
Process creation errors occur as we to build our query. These are driven by either errors in the M code, or users not understanding how Power Query operates.
M code errors
M code errors can be challenging to find, especially if you’re new to the language. A comma or mistyped word is enough to cause the process to fail. The three main places where we can edit the M code:
- Custom Columns
- Advanced Editor
- Formula Bar
Let’s start by looking at Custom Columns, then move onto looking at the Advanced Editor and Formula Bar together.
Of the M coding options, the Custom Column feature is the most accessible and the one we are more likely to use
Custom Columns contain a warning at the bottom of the screen to help guide us with our formulas. Unless we’ve been working with Power Query for a while, we won’t understand what many of these messages mean, and even then, those error messages can be confusing.
The screenshot above shows the Token RightParen expected message. This is just one of many potential messages. As we type into the formula box, the message will change. Therefore, it is not worthing looking at this message until we think the formula finished. If the Show error option is visible, we can click it to take us to exactly where the problem is.
Once you know what the messages mean they are not as difficult as might 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 a function.
- Token Then expected, or Token Else expected means the words then or else are expected to be entered next. 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 there is an issue with the value entered as the literal (this often occurs when a test 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 invalid).
- The formula is incompleteis usually an indication that no formula has been entered (only the equals symbol in the formula box).
The newer versions for Power Query include IntelliSense, which significantly reduces the risk of error. It helps to identify the arguments and data types required for each function, but also helps to minimize typos.
Once we get the message which says No syntax errors have been detected, we can click the OK button to close the window. This doesn’t mean the formula or data types are correct, but that the syntax has been entered correctly.
Advanced Editor & Formula Bar
The Advanced Editor and Formula Bar do not have the same level of checks as a Custom Column; you can accept changes even if it causes an error. 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 Done to be clicked even if there is an error in the code.
- The Formula Bar has no error checks, so you can make any changes to the code and press the Enter key to accept those changes.
Given the multitude of possible errors you could create, I can’t go through all of them. However, once you know how to read the error message, it is much easier to troubleshoot.
The screenshot below shows an Expression.SyntaxError… hmmm… what does that mean? If you look further down in the code, Power Query has kindly shown us where the error is. The numbers on the left show the line numbers in the code. If you notice, there is a break in the numbers with an arrow —->, this is to indicate that this line highlights where the error is. By looking along that line, we will find a group of ^^‘s, these pinpoint the exact place where the error resides.
In our example above, the error is that we have used a data type of dat, which is not valid.
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.
There is a very frustrating error, which will rear its head from time to time: Formula.Firewall: Query ‘[Name of query]’ (name of step) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
What does that mean? And how can you fix it?
Power Query does not like to use two data sources together where the privacy settings are different. This usually occurs when there are external and internal data sources used in the same query.
Option 1: Ignore the Privacy Settings
This first option is not ideal, as it ignores the data privacy settings. However, if you are the only person accessing the data, then it’s a useful little fix.
Click File -> Option Settings -> Query Options
The Query Options window will open. Select Privacy -> Always ignore Privacy Level settings, then click OK.
Refresh the data to update the query. The data now load correctly.
Option 2: Split the data load into separate queries
Rather than trying to describe a complex solution here. I will point you to two resources which provide the solution:
- Power Query Errors: Please Rebuild This Data Combination – Ken Puls (excelguru.ca)
- Formula.Firewall Error in Power Query & Power BI: Rebuild This Data Combination Solved – Mike Girvin (ExcelIsFun YouTube Channel)
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 the transformation steps expecting to find a column called “Product”, but a “Product” column does not exist in the data set. Neither 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 either a file or database has changed location or where 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 that it cannot find.
We will also see an error in the Queries & Connections window. If we double-click the query, we will find out more detail about the error.
The Power Query editor will open and show the following message. Click Go To Error to be taken to the exact step.
Finally, you can click the Edit Settings to change the source location in the window.
Generally, Column header names are hardcoded somewhere within the M code. Therefore, any changes in the 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 will reveal further details about the error.
To fix the error, we can either:
- Change the header name in the source data
- Correct the hard-coded value within 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.
Whichever option you take, you need to be careful. Poorly implemented changes can cause other problems further down in the query. The good news is that you can always discard changes and start again.
Data type errors will not prevent the data loading into the query; instead, those cells will be 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 non-numeric data types
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.
Clicking the “Errors” link within the Queries & Connections pane will open a query showing all the errors.
As can be seen in the screenshot above, Power Query will show where the errors and, and even provide the Row Number so you can identify the issue with the source data. After clicking the word “Error” within the Preview Window it will provide details about the specific issue.
In the screenshot about, we can see that Power Query was trying to convert the text value of “David” into a Number, which can’t be done.
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.
When I started using Power Query, I came across two software bugs. In both cases, I concluded that 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 being updated, bugs can come and go quickly as newer versions are released. Over the past few years, I would say the software has become robust and now rarely suffers from issues.
Hopefully, you will not encounter any of the problems I had; they have already been resolved. Therefore, if you do encounter an issue where the software is not behaving as documented, then updating to the newest version should resolve the issue.
Power Query Series Contents
- Import Data
- Data Refresh
- Edit Queries
- Close & Load Options
- Using Parameters
- Basic Transformations
- Combine / Append Queries
- Import All Files in a Folder
- List All the Files in a Folder and File Attributes
- Import Data from the Current Workbook
- Import Data from the Web
- Unpivot Data
- Unstacking Data in a Column
- Lookup Values using Merge
- Change the Source Data Location
- If Statements for Conditional Logic
- Grouping and Summarizing Data
- Custom Functions
- Common Errors & How to Fix Them
- Tips and Tricks
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: