March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to 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
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
Amazing, thx!!
THIS IS BRILLIANT!!!!!
@Smauro Hello!
Thank you so much for your help! This function is so convenient!!! Just gave it a try, so simple!
Thank you!!!!
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.
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
20 | |
13 | |
10 | |
9 | |
7 |
User | Count |
---|---|
39 | |
27 | |
16 | |
15 | |
10 |