Power Query: Trying to Extract Errors

Welcome to our Power Query blog. This week, I look at how to view the errors for each row.

I have some data for my tent equipment company. In particular, the volume column contains some inconsistent entries, viz.

I pull this data into a query using the ‘From Table’ option in the ‘Get & Transform’ section on the ‘Data’ tab.

I have tweaked the ‘Changed Type’ step, and now two of my rows have errors.  The M code behind the step is:

Highlighted in Green, the volume(m2) type has changed to Int64.Type, which means that the rows containing letters don’t work.  However, I’d like to see what error has been issued. I can do this by clicking on the ‘Error’ cell.  

This tells me the error, but only for one row at a time – I want to see all of the error reasons. One way I can see how many errors have occurred for my data, is to load and view the query from the ‘Workbook Queries’ pane in the Excel worksheet.

I can see that four rows are loaded and there are two errors. If I click on ‘2 errors’, I can look at which rows have errors.

What I’d like though, is to see all of my data and the reason for the errors. I can do this by adding a custom column from the ‘Add Column’ tab in the Power Query window.

I add a column which will ‘try’ to calculate the column volume(m). This gives me a record and I need to expand it to get the information I want. The reason I get a record is explained in the definition of the ‘try’ function taken from Microsoft’s help pages:

A try expression converts values and errors into a record value that indicates whether the try expression handled an error, or not, and either the proper value or the error record it extracted when handling the error.

I want to extract the value of the error.

I know which column ‘HasError’ and I certainly don’t want to ‘use original column name in prefix’. There is a catch – the warning message that the ‘List may not be complete’ is particularly relevant here: I need to use the ‘Load more’ option:

I need the last entry on this column – ‘Error’.

I get another record, which again I need to expand.

I am going to select all these fields to see what I can find out about my errors.

I can see the Power Query error type ‘Dataformat.Error’, the description of the error ‘We couldn’t convert to number’, and most importantly, the original value. In this case, replacing my errors with zero would have worked for the ‘n/a’ cell, but not the ‘0.5m’ cell, as I need to remove the ‘m’ but not the number.

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