Power Query: Erasing Errors

Welcome to our Power Query blog. This week, I look at how to deal with imported errors.

I have some employee data that I need to import and clean up. To show the process, I am going to upload it in its current error-ridden state:

Ignoring the problems for now, I choose to create a new query from the ‘Get and Transform’ section of the ‘Data’ tab, in this case using ‘From Table’:

Since I am not going to go back and work out what caused these errors, I simply want to set them to zero (0):

So, I’ve replaced the errors and I’m happy… well not quite.  If I look at the M code generated for this step I see

My column names are referenced.  I want it to be more robust than this – I want to change all errors into zeroes.  I also want it to cope if the column names were to be changed.  I am going to replace the line of M code (above) with some different code.  To show what each step will do, I will create the first steps as custom columns, which I will remove at the end when I apply the code directly to the Advanced Editor.

I begin by creating a new custom column from the ‘Add Column’ tab, that will contain all of my column headings.

Now, I want to create a new custom column containing a pairing which says that each column is linked to a zero value. I will use this later to make my substitution.

This gives me my substitution column.

My final step is to apply the substitution. I am not going to do this as a column, as I now want to create the solution as it would be more useful, and to do that I am going to apply the steps directly to the Advanced Editor. I am removing any step that references column names (such as ‘Changed Type’), since I want this query to apply to any similar data without worrying about column names.

When I apply my changes I get the transformed table, viz.

I should now be able to change the name of a column in the original source without affecting the query.

I’ve changed Feb-18 to Mar-18, now I will see what happens in the query…

The query is working independently of any column name changes. Result!

Come back next time for more ways to use Power Query!