Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

Power Query – Common Errors & How to Fix Them

Power Query - Common Errors

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.

Custom Columns

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.

Custom Column Error

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.

Advanced Editor Error

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.

Formula.Firewall error

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.

Formula.Firewall Error

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

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:

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.

Source Data Error Message

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.

Source Data Did Not Download

The Power Query editor will open and show the following message.  Click Go To Error to be taken to the exact step.

Power Query - Source Data Error (Go To Error)

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

Power Query - Source Data Error (Edit Setting)

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.

Column names

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

Column Name Changed

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.

Column Name Changed - Error Messages

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 types

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.

Data loaded 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.

Clicking the show errors preview window

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.

DataFormat.Error Message

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.

Software bugs

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.

Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

Don’t forget:

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:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. 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.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

One thought on “Power Query – Common Errors & How to Fix Them

Leave a Reply

Your email address will not be published.