Forum Discussion
List all errors in a table
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
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
5 Replies
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
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.
