List all errors in a table

[primolee]

Hello there,

Is there a way to keep rows with errors and also keep the cause of these errors in a column?

Because I have 60 columns, if there are 2 or more errors in a row, is there a way to keep these error messages in a column or list?

Thank you so much for your time and help.

Best Regards,

David

[Smauro · Accepted solution]

Hi @primolee 

I use this function sometimes:

(table as table) as table =>
    let
        errorExtract = (value as any) as any => if value[HasError] = true then value[Error] else value[Value],

        errorToText = (errorRec as any) as any => if Type.Is(Value.Type(errorRec), Record.Type) then "Reason: " & errorRec[Reason] & "#(lf)Message: " & errorRec[Message] & "#(lf)Value: " & (try Text.From(errorRec[Detail]) otherwise try Text.From(errorRec[Detail][Value]) otherwise null) else errorRec,

        recsToErrors = (rec as record) as any =>
            List.Accumulate( Record.FieldNames(rec), [],
                (s,c) => Record.AddField(s, c, errorToText(errorExtract(try Record.Field(rec, c)) ) ) ),

        
        #"Error Table" = Table.FromList(List.Transform(Table.ToRecords(Table.SelectRowsWithErrors(table)), each recsToErrors(_)), Record.FieldValues, Table.ColumnNames(table))
    in
        #"Error Table"

It keeps only error rows and transforms errors to the text of the error record. It may be useful to you.

Best,

Spyros

[Anonymous]

Amazing, thx!!

[TrevorC]

THIS IS BRILLIANT!!!!!

[primolee]

@Smauro  Hello!

Thank you so much for your help!  This function is so convenient!!!  Just gave it a try, so simple!

Thank you!!!!

[PhilipTreacy]

Hi @primolee 

Yes, please refer to this article

 https://www.sumproduct.com/blog/article/power-query-blogs/power-query-trying-to-extract-errors

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.