Handling Errors and Debugging

Error handling in Power Query’s M language involves techniques for managing and resolving errors that may arise during the evaluation of an expression. By utilizing expressions and functions such as try, otherwise, and catch, you can gracefully handle potential errors and effectively control the execution flow. Additionally, the Power Query editor offers a convenient built-in feature called the Applied Steps pane, which allows you to step through the code, facilitating the identification of issues within your query.

This chapter provides you with an overview of the error-handling capabilities and debugging strategies in Power Query. It aims to equip you with the necessary knowledge to effectively address errors and ensure your queries are more future-proof. By understanding common errors that can occur in the M language and applying the techniques discussed here, you will establish a solid foundation for handling errors and creating more robust...

Technical requirements

To make the most of this chapter, we encourage you to open your favorite Power Query editor and try out the provided samples. By executing and exploring these scripts, you will gain a deeper understanding of how to handle errors and debug.

What is an error?

In Power Query’s M language, one of the fundamental building blocks is the expression, which is responsible for producing a value upon evaluation. When an error is encountered, it indicates that the evaluation of the expression failed and could not be completed successfully. Errors can have various causes, such as invalid identifiers or operations, incompatible data types, and more. Understanding, preventing, and effectively handling errors is crucial for creating queries that are more robust and reliable.

When evaluating an M expression, there are two possible outcomes:

• The production of a single value

  This indicates that the evaluation process was successful, and the expression was able to generate a result.

  let
      result = 1 / 0
  in
      result
  

  Division by zero does not raise an error but returns infinity.

• Raising an error

  When an error is raised, it indicates that the evaluation of an expression failed to...

Error containment

Power Query’s M language integrates containment-based programming principles. Let’s explore what that means for the evaluation of expressions.

Containment and propagation means that even if an error occurs while evaluating a particular value or member expression, it doesn’t necessarily stop the entire evaluation process. Instead, the error can be “contained” without automatically propagating to the top-level expression.

When an error occurs, it will cause the evaluation of the current member expression to stop; it effectively unwinds, or reverses, through the previously evaluated parts of the expression. However, as long as an error can be contained, it does not propagate to the top-level expression and a value may still be produced.

Imagine you have a table that contains a cell with an error; this error can be contained within a record field. The entry will be marked as having an error, and the error record will be...

Error detection

When working in Power Query, encountering errors is a natural part of the development process, regardless of whether you design queries through the user interface (UI), modify existing code, or write M code from scratch.

When you add “real-world” data into the mix, errors become a part of life that you have to deal with and manage, a process that starts with error detection.

Errors are classified into two types:

• Step-level errors
• Cell-level errors

What separates the two is their containment.

Step-level errors propagate to the top-level expression and cause the evaluation of the entire query to terminate, causing the query to fail. To address step-level errors, it’s essential to determine where the error was raised. The Applied Steps section within the Query Settings pane is where you need to go for a step-by-step evaluation of your query. By selecting each step and evaluating it, starting all the way at the...

Raising errors

In Power Query’s M language, you can define custom errors. This is helpful when you want to provide more specific and meaningful error messages, be able to handle exceptional cases, enforce custom data validation rules, or improve the overall user experience for example.

The error expression

To raise an error, simply call the error-raising expression error and use a text value to provide the desired error message you want to display. For example, this expression will raise the following error:

error "Invalid data, you did not provide a table."

Errors provide an error record; therefore, you can also specify a record to include more information about the error. This error record should contain fields such as Reason, Message, and Detail. Here’s an example of how to raise an error with a custom error record:

error [
    Reason = "Invalid data, you did not provide a table.",
    Message = "The data provided is not...

Error handling

Implementing error-handling strategies extends beyond addressing raised errors alone. It is as much about making choices and using techniques available in the M language to effectively mitigate errors and ensure the robustness of your data transformation workflows. When working with data, it’s common to encounter errors such as missing values, incompatible data types, and unexpected or invalid calculations. To address these challenges, every technique should be considered. The aim is to implement error-handling strategies that will help avoid, manage, and resolve errors right where they occur, ensuring a predictable outcome as a result. Here’s what is in your toolkit:

• Coalesce, dealing with nulls

  Power Query’s M language supports null propagation, which means that if a value is null in a sequence of operations, the result will be null. The a field in myRecord illustrates this. It is this behavior that helps to prevent errors that...

Strategies for debugging

Debugging is an integral part of the development process, and encountering situations where the code behaves unexpectedly is inevitable. Effective debugging strategies play a crucial role in identifying and rectifying errors while also enhancing code comprehension. General best practices for debugging M code include the use of comments, renaming steps for readability, breaking down complex expressions, and testing.

Let’s look at them in greater detail:

• Comments are not just placeholders for thoughts or code descriptions; they serve as critical pointers that guide the debugging process. They can help you and others understand the purpose and functionality of different code sections, making it easier to locate and fix issues. Moreover, comments can be used to mark areas of code that require further review or are prone to errors.
• By default, Power Query assigns generic names to each step. These names don’t provide much insight...

Common errors

As you encounter and handle errors in your Power Query workflows, it’s essential to review patterns and learn from them. Analyzing common errors and understanding their root cause can guide you in refining your error-handling approach. Therefore, this section focuses on some of the most common errors you are likely to encounter. Furthermore, it is helpful to understand that multiple errors can be encountered during debugging, and syntax errors will always be raised first as they make it impossible for the M (mashup) engine to parse the provided code.

Syntax errors

Syntax errors in an expression are typically identified during the code-writing phase. These errors indicate that there is an issue with the pattern or structure of the provided expression.

A missing comma on line 3 returns Expression.SyntaxError: Token ',' expected:

let
    A = 1,
    B = 0
    result = A / B
in
    result

An additional comma inside the list on line 2...

Putting it all together

Throughout this chapter, you have gained insights into various aspects of error handling in Power Query. You have learned about the nature of errors, how to raise them intentionally, as well as how errors are contained, detected, and ultimately handled. With this foundational knowledge in hand, it is time to consolidate your learning by delving into two practical examples that showcase real-world scenarios of error handling in action. These examples will provide a deeper understanding of how to prevent errors, make deliberate choices, and address errors when they arise.

Feel free to design your own approach to these scenarios before diving into them. If you are not comfortable translating that into M code, that is absolutely fine. You can still create an outline and think of obstacles that have to be overcome. This will allow you to compare your strategy to the one suggested here. But remember, there are a multitude of solutions to any given problem, and...

Summary

This chapter started out by creating a foundation: a shared understanding of what an error is, and how errors can be raised, contained, and detected. It explored various aspects of error handling, highlighting the importance of adopting a holistic approach. Beyond reacting to errors alone, it is crucial to proactively incorporate preventive measures into your query design. By employing a combination of these techniques thoughtfully, you can create queries that are more resilient, thus reducing the risk of errors and improving overall data quality.

In the next chapter, we will learn about the major functions and operators that allow iteration and recursion in Power Query M.

Learn more on Discord

Join our community’s Discord space for discussions with the author and other readers:

https://discord.gg/vCSG5GBbyS