Error.Record

Updated on

Error.Record is a Power Query M function that creates an error record from the provided text values for reason, message, and detail. The function returns an error record with the specified values.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Error.Record(
   reason as text,
   optional message as nullable text,
   optional detail as any,
   optional parameters as nullable list,
) as record
ArgumentAttributeDescription
reasonA text value with the reason for the error.
message optionalYou can optionally provide a message that gives context to the error.
detail optionalUsed to provide additional details for the error. You can provide a text value, or alternatively providing a record allows you to specify multiple items.
parameters optionalAllows you to provide a list of values that you can dynamically insert into the message. When used, you can reference values within the message argument by writing #{0} or #{1} for the first two values.

Description

The Error.Record function returns an error record from the provided text values for reason, message and detail. It creates structured error records, allowing users to specify a reason, message and detail about the error. You can use the optional parameters argument to make the output of the message argument dynamic.

When called, the function returns a record containing fields such as Reason, Message, and Detail. This structured record can then be used to raise informative errors in data transformation processes.

Examples

Let’s look at a few examples to learn how to use the Error.Record function.

Starting Simple

You can create an error record with only a reason text by filling in the first argument only:

At its core, Error.Record creates a structured error record. If you just give it a reason, it looks like this:

Error.Record(  "Permission Denied" )

/* Output: 
[ 
   Reason = "Permission Denied",
   Message = "",
   Detail = null,
   Message.Format = null,
   Message.Parameters = null
]  */

Want to raise an actual error with this? Add error before your code:

Error.Record returning an error by prefixing with error

This returns the error message :”Permission Denied:”.

Adding a Message

If you’d like to be more descriptive, you can include both the reason and the message.

Error.Record(
  "Permission Denied", 
  "You don't have access to this resource."
)

/* Output: 
[ 
   Reason =  "Permission Denied",
   Message = "You don't have access to this resource.",
   Detail = null,
   Message.Format = null,
   Message.Parameters = null
]   */

To raise an error, prepend with the text error:

Error.Record function with optional message

This returns the error message: “Permission Denied: You don’t have access to this resource.“.

Diving Deeper with Details

Next, you can also provide a details for your error message.

Error.Record(
  "Data Mismatch", 
  "Expected numeric data but received text.", 
  "Received: ABC"
)
/* Output: 
[ 
   Reason = "Data Mismatch",
   Message = "Expected numeric data but received text.",
   Detail = "Received: ABC",
   Message.Format = null,
   Message.Parameters = null
]
*/

Turn it into an error and you see:

Error.Record returning an error using reason, message and detail parameters

Sometimes you might want to provide a more complex detail structure. You can do that by providing a record in the optional detail parameter.

Error.Record(
    "Data Validation Error",
    "The data in the column does not meet the criteria.",
    [Column = "Age", Criteria = "Must be > 0", ReceivedValue = "-5"]
)
/* Output: 
[ 
   Reason =  "Data Validation Error",
   Message = "The data in the column does not meet the criteria.",
   Detail = [Column = "Age", Criteria = "Must be > 0", "ReceivedValue = -5 ],
   Message.Format = null,
   Message.Parameters = null
]
*/

This creates an error with multiple fields and detail messages.

Error.Record function with complex optional details using a record

Dynamic Messages with Parameters

For more flexibility in creating your error message, you can provide the Error.Record function with parameters in the 4th argument. Here, you provide a list of values. You can then dynamically let this values from this list come back in the message. Adding #{0} returns the first value from the list, wheres #{1} returns the second, and so on.

Error.Record(
    "Type Mismatch", 
    "Expected #{0} but received #{1}.", 
    "Type error in column 'Price'", 
    {"number", "text"}
)

The output of this function is:

Error.Record function returning record with error information

Prefix the code with the word error and you receive below error message:

Error.Record function with custom message format using Parameters

The above examples show you different formats to create precise, and dynamic error messages. Remember, errors are your way of communicating problems, so the clearer they are, the better.

Other functions related to Error.Record are:

BI Gorilla Youtube Channel

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/error-record