In Power Query, an error has a reason, a message and details. Often, all three values are strings of text—but did you know this doesn’t have to be the case? Detail can be text or a record. This fact makes it easy to pass structured information for an error’s details, which the UI and logging will reflect appropriately.
A typical error, with all fields provided as text values (so no record used for Detail):
error [
Reason = "Business Rule Violated",
Message = "Value must start with a letter",
Detail = "Non-conforming value '456'"
]
In contrast, an error with its Detail defined using a record:
error [
Reason = "Business Rule Violated",
Message = "Value must start with a letter",
Detail =
[
BadValue = "456"
]
]
Representing error details in a structured way—that is, by providing Detail as a record—makes them (much) easier to programmatically read and manipulate.
Imagine that errors like the above are found scattered across the columns in a table. You want to output an overall list of bad values. It’s not super hard to get a list of errors from across a table’s columns, but then what?
If those error’s details are just text values, you need to parse each textual detail message to extract the bad value. When those strings all follow the simple format shown in the first example, doing this isn’t too bad, but if the format is a little more elaborate, parsing can become much more complex.
In contrast, if those details follow the “enhanced with a detail record” pattern we’ve been discussing, no parsing is required: the data of interest is already segmented away in its own record field. You simply need to access that field to get the value of interest!
List.Transform(ListOfErrors, each [Detail][BadValue])
Bonus: Since Detail can contain a record, and a record can itself contain records, an error’s details can contain a nested set of records!