One of the great features of Power Query is the way you can view any rows that contain error values when you load data. However, even if you can see the rows that have errors you can’t see the error messages easily – without writing a little bit of M code, which I’ll show you in this post.
Imagine you have the following table of data:
…and you load it into Power Query using the following query, which sets the data type for the Sales column to be Whole Number:
[sourcecode language=”text” padlinenumbers=”true”]
let
Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Sales", Int64.Type},
{"Product", type text}})
in
#"Changed Type"
[/sourcecode]
As you’d expect, the last two rows contain error values as a result of this type conversion:
You can also see the number of rows that contain errors when you load the query:
Clicking on the “2 errors” link in the screenshot above creates a new query that only contains the rows with errors:
You can click on the Error link in any cell that contains one to see the error message:
But what you really want is to see the error message for each row. To do this add a new custom column with the following definition:
try [Sales]
You will then see a new column called Custom containing a value of type Record. You can then click the Expand icon in the column header (highlighted) and then OK:
You’ll then see another column called Custom.Error with an Expand icon; click on it and then click OK again.
And at last you’ll have two columns that show the error messages for each row:
Thanks Chris. Very interesting. Actually I find Power Query error handling to be very unpleasant and unsatisfying. I have had experiences that when there is an error, the whole thing can fail and stop. I can think of specific examples where I have used a function to convert a file format, and then call that function over a list of excel files in a folder (using a process like this one described here. http://exceleratorbi.com.au/consolidate-multiple-excel-files-in-sharepoint-using-powerquery). I remember there were DIV/0 errors in the first file, and Power Query then refused to import any more files. I tried using “ignore errors” but this didn’t seem to work. I ended up having to rewrite all the formulae in the source Excel files to trap for DIV/0 errors before Power Query would work.
Have you experienced such issues? Shouldn’t the “remove errors” button remove the errors – presumably replacing them with null?
you can either “Remove Errors” for the whole query or “Replace Errors” for a single column
I guess “Replace Errors” is the way to go in your case. you find it in the context menu of a column and not in the ribbon
you may also use a use try/otherwise command like “try [MyCol1]/[MyCol2] otherwise 0” for your calculated column
hth,
gerhard
Matt, PQ expects you to know exactly what you’re doing when you’re invoking a function, otherwise the results might not be what you actually want so in 99% of those cases where you get an error from invoking a function the reason behind that error is that the function is trying to be applied to a file or a table that doesn’t contain the expected format described in the function – basically the function has a fixed format and it was applied to a file that didn’t meet that fixed format.
This is probably why I always try to go with the Excel.Workbook() on a row by row basis instead of invoking a function (besides that it runs faster that way). Another way of making your function work would be the read all of those columns as text and later, when you have them consolidated, get rid of the ones that are not numeric using another column for a true-false.
BTW! you might want to check out why, in some cases, it is better to try the function approach with a fixed format:
http://www.mattmasson.com/2014/11/iterating-over-an-unknown-number-of-pages-in-power-query/
I’m still waiting on Chris to post more about this lazy evaluation (and partially lazy) concept in PQ 🙂
Very much reminds me of my last years post http://blog.gbrueckl.at/2013/12/error-handling-in-power-query 😀
Oh yes, sorry!
No worries 🙂
Thank you for this post.
I’m facing a more severe issue where PQ tells me that the result of a query has 9 errors.
When I double-click the hyperlink of the “9 errors”, an “Error in…” query window is displayed.
It has 4 applied steps: Source(the original data), Added Index (don’t know what it is), Kept Errors, Re-ordered Column.
The last 2 steps are empty.
How can I see what the errors are actually?
It’s quite frustrating 🙂
Thanks a million in advance.
By ’empty’ do you mean you see no rows of data? The ‘Added Index’ column is just adding a row number to the table returned; the ‘Kept Errors’ step should be where your input data is filtered to only leave the rows that contain error values.
Correct.
Thanks for the reply.
I will check the kept rows step again.
Hi all, I’ve stumbled on an awkward one here where there are actually no errors kept under ‘Kept Errors’ – or any other stage. The query only returns say 20 rows and I can see there are no errors in the final table but 5 are reported during the load. I tried refreshing again a couple of times but they are still there!
The errors are all in the same column and they are (or supposed to be!) text values. If anyone want to take a look I’d be happy to share.
… I forgot to mention, when I deleted the table and loaded it again the correct values show up in the new table for a split second before being converted to errors.
The table is dynamically created from columns from other tables selected in a parameter table, so if it is a type problem then I’m not sure how I would detect the appropriate type and apply it to the (unknown) columns using a transformation. If anyone can shed light on how to do this I will give it a go.
Feel free to send me a repro and I’ll take a look when I have time. You can contact me through the “Contact” page above, or via http://www.crossjoin.co.uk
Chris. This blog post gets me closer to what I’ve been trying to accomplish with kept errors. However, the data I’m working with can have errors in different columns. What I would like to do is to generate a report that shows the column name that has the error and the record number that an individual can use to find errors and do clean up activities instead of simply removing the errors.
Hi Chris, thanks for the article. Would you know why a Desktop refresh would flag such errors (“X of the loaded queries contained errors”) but the online dataset refresh completes successfully? Thanks!