Power Query: Erasing Errors
26 September 2018
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/09-sep/95/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
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’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/09-sep/95/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
Since I am not going to go back and work out what caused these errors, I simply want to set them to zero (0):
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/09-sep/95/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
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
Table.ReplaceErrorValues(#"Changed Type", {{"Feb-18", 0}, {"Average 2018", 0}})
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/09-sep/95/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I begin by creating a new custom column from the ‘Add Column’ tab, that will contain all of my column headings.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/09-sep/95/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/09-sep/95/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
This gives me my substitution column.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/09-sep/95/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/09-sep/95/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
When I apply my changes I get the transformed table, viz.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/09-sep/95/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
I should now be able to change the name of a column in the original source without affecting the query.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/09-sep/95/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
I’ve changed Feb-18 to Mar-18, now I will see what happens in the query…
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/09-sep/95/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
The query is working independently of any column name changes. Result!
Come back next time for more ways to use Power Query!