Viewing Error Messages For All Rows In Power Query

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:

image

…and you load it into Power Query using the following query, which sets the data type for the Sales column to be Whole Number:

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(
                                      Source,
                                     {{"Sales", Int64.Type}, 
                                      {"Product", type text}})
in
    #"Changed Type"

 

As you’d expect, the last two rows contain error values as a result of this type conversion:

image

You can also see the number of rows that contain errors when you load the query:

image

Clicking on the “2 errors” link in the screenshot above creates a new query that only contains the rows with errors:

image

You can click on the Error link in any cell that contains one to see the error message:

image

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]

image

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:

image

You’ll then see another column called Custom.Error with an Expand icon; click on it and then click OK again.

image

image

And at last you’ll have two columns that show the error messages for each row:

image

12 thoughts on “Viewing Error Messages For All Rows In Power Query

  1. 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 🙂

  2. 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.

  3. 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.

  4. … 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s