#NULL!, #REF!, #DIV/0!, N/A .... Data tables regularly contain errors that limit data analysis. Fortunately, it's possible to correct them through Power Query Error Handling. DataScientest shows you how.
Power Query Error Handling
There are two main types of error in the Power Query editor: step and column errors.
Stage level
If an error occurs in the step, the request cannot be loaded. You will then see an error message appear in a yellow window.
This provides information on the various components of the error: reason, message and details
And if you need more information, you can also click on the “Access error” button. It’s all this data that enables you to adopt effective error management in Power Query.
Here are the most common errors:
- Source not found: this error appears if you can’t access the data source, if you don’t have identification information or if the source has been moved. In such cases, you should modify the access path.
- Column not found: this is what happens if you’ve specified a column that doesn’t exist in the query. There are several possible solutions here, including deleting the step referring to the column in question.
- Formula.firewall: this error occurs when you combine or merge data from several sources. Before applying a solution, you need to understand the exact cause of the problem by accessing the data confidentiality firewall.
Column level
In this case, the query loads, but the value appears as an error in the corresponding cell. To understand the origin of the error, you can click on the white space of the cell concerned (a new yellow window will appear) or use the data profiling tools.
These errors can be linked to a data type conversion problem, unsupported operations, nested structured values, etc.
Whatever the problem, there are a number of error management solutions available with Power Query. Here’s what you need to know:
- Deleting errors: select the column concerned, then go to the “Reduce rows” group under the “Home” tab. Click on “Delete rows”, then on “Delete errors”.
- Replacing errors: select the column concerned, then go to the “Any column” group under the “Transform” tab. Click on “Replace values”, then on “Replace errors”. Then enter the new value (as an integer, custom text, etc.).
- Keeping errors: this makes it easier to identify lines containing errors. The procedure is as follows: Home > Reduce lines > Keep lines > Keep errors.
Customized Power Query Error Handling
Beyond the above solutions, it’s also possible to handle errors with the Power Query editor by adopting its own logic.
Here’s how to do it:
- Replace an error with another value: to do this, simply add a custom column with different options. So, in the “Custom column” window, you can enter the query:
try [column-1] otherwise [Column-2]If no error is found in column-1, its data will appear. Otherwise, the data in column-2 will appear.
- Customize new results : again, you need to use the custom column, but the query differs:
try [column-1] catch (r)=> if r[Message] <> "Invalid cell value '#REF!'." then [column-2] else null.In this case, column-1 will be replaced by column-2 only if the error message is different from #REF! ( #NULL! or #DIV/0!). Otherwise, column-1 will be replaced by “null”.
To effectively manage all these errors with Power Query, it’s best to get trained. With DataScientest, you can do just that. Through our programs, you’ll learn to master the query editor and numerous data tables. Join our training courses.
Things to remember :
- Given the sheer volume of data contained within a table, errors are very common. This can limit data processing and analysis.
- Fortunately, efficient error management is possible with Power Query.
- The query editor provides several manipulations to correct bugs according to the type of error.
- You can also customize the correction options to suit your needs.
