Query Error Auditing in Power Query for Excel and Power BI

As any other programming language, Power Query handles errors in its own unique way and the goal of this blog post is to give you a few hints on how to audit the errors or warnings that Power Query might throw your way.

Types of Errors

Power Query primarily handles 2 types or categories of errors:

• Step level Errors – it’s main symptom is that you query simply will not load giving you a message similar to the one below

• Value Level Errors – query will load, but will have a warning saying that it loaded with errors and give you a hyperlink to see what errors it had when loading. For example, you could click on the “1 error” hyperlink shown in the image below and Power Query will create a new query with only the rows that have errors so you can audit those rows specifically.

Ways to Audit Errors

The best way to audit the errors is to go into the Power Query editor window (fka Query Editor window) and go through the steps and values to read the error messages.

For both categories or types of errors, it is extremely important to understand the Error Message which provides a path (exactly where it happened, usually the first sentence in the error message) and checking the Error Reason (exactly why it happened, usually in the details section of the error message).

An example of a Step level error with its Error message is shown in the next image:

In some cases Power Query even gives us this “Go To Error” button so we can go to the first step where we hit the error so we can fix the issue. If Power Query doesn’t give you that button, or it doesn’t get you to the first step where you error was raised then it is helpful to navigate through the steps to find out where the error first appeared.

For Value Level errors the hyperlink to the errors is an invaluable help. Clicking that hyperlink will make Power Query automatically create another query, usually with the Format “Errors in [Name of Original Query]”, with only the rows that had errors in your original query and it’ll also add an Index Column (called Row Number) to tell you exactly in what row the error happened. You can click in the whitespace next to the value errors to see the Error Message in the Cell Preview pane as shown below.

One thing to mention is that you could’ve manually gone through your original query, select the fields that you want to audit and select the option called “Keep Errors” so you can only see the errors found in those specific fields/columns:

The Most Common Errors in Power Query

From my experience over the years, there are only a handful of errors that you’ll encounter in Power Query. There are so few that I created the next list of the most common errors that might come across when working with Power Query.

Most Common Step Level Errors

1. A Data Source function Error – usually caused by Power Query not having the right credentials or unable to connect to the data source (wrong file path or server name in most cases). Example: A query is being pointed to a filepath that no longer exists and, since Power Query can’t find it or connect to it, it displays a DataSource Error as shown below

1. Missing Columns Errors – usually caused when a step is referencing a column that no longer exists. Example: The report was doing a fill down operation over a column that was named “Employee”, but suddenly the files had a change so that column would have the name “Full Name” instead. This discrepancy gives us the errors below

Most Common Value Level Errors

1. Conversion Errors – converting a text that isn’t a date to a date data type can bring an error. When a value can not be converted to the desired data type, its output will be an error as shown below (Power Query can’t convert the text string ‘——‘ to a date)

1. Operation Errors – when a operation or a function requires a specific data type for a value, but we pass a completely different data type, then its output will be an error value. Example:  in the next image you’ll see that I try multiplying a column that has a text value “1” against a column that has a numeric value 1. Since the Column1 is set to text, that is not the number 1, but just a text “1”, so that operation yields an error and the Details tells us that the operator (*) can’t be applied to it. Similar to this situation, you can find others with functions that only accept certain data types and we try to pass a completely different data type that causes errors.

To learn more cool techniques and more advanced scenarios with the M language, go ahead and enroll to the FREE trial of the Power Query Academy where I talk more in detail about these type of techniques.