You’ve built a custom connector—and it works great! But regardless of how good your query folding implementation is, likely there are times that a hand-crafted native query can beat it in performance and capability. So, in addition to supporting query folding, you’d like to give users the option of using native requests that they write with your connector.
In a nutshell, you’re hoping to enable something like:
let
Source = MyConnector.DataSourceFunction("some-host"),
Results = Value.NativeQuery(Source, "some native request goes here")
in
Results
Is this possible? From Power Query’s perspective, at the technical level: Yes—and it is easy to pull off.
A Power Query mashup expression dies with an error. As the error propagates through your code, did you know that it sometimes collects a “journal” of the locations (e.g. line numbers) it passes through? This “expression stack,” as it’s called, can be used to help identify the troublemaking line of code.
An error’s expression stack is not automatically exposed in any user interface. Its hidden presence suggests that it may be a component supporting some past, present or planned UI functionality (perhaps it’s part of powering the “Go to Error” button?). Even though it is hidden, there may be cases where you find the location details it contains useful when debugging. Even if not, knowing about it is interesting Power Query trivia. 🙂
In the real world, errors are a part of life. If you access and read data from real, in-production systems, sooner or later you will almost certainly encounter errors. While you may be unable to escape their unfortunate reality, at least in the Power Query world, they’re rendered out in an easy-to-read format:
Easy to read, that is, if you are a human, reading just one error all by itself.
But what if you’re trying to analyze a collection of error messages? Imagine a set of errors like the above, but which are for a variety of different codes and problems (e.g. bad code ‘A235’, problem ‘must contain at least 2 letters’, bad code ’15WA’, problem ‘cannot start with a number’, etc.).
Let’s say you want to summarize these errors, reporting out the count of errors per problem, per bad code. Manually reading errors one at a time no longer cuts it. Instead, you could write code that parses each error message, extracting the text between the phrase bad code ‘ and the following quote character, and between problem ‘ and the following quote character. With the code and problem statement now separately captured, you can use their values to group by or otherwise compute the desired summaries.
Parsing log messages like this this involves coding work. Not only does it take effort on your part, but it is also tricky to get right. For example, the logic described above finds the end of each string it matches by looking for the next quote character. What if a bad code or problem description includes a quote character? The logic we’ve been considering won’t match the entire value. Say, the message starts with Bad code ‘ABC’DEF’. The above logic will miss the second portion of the code (only capturing ABC, not the full ABC’DEF) because it incorrectly assumes that a bad code will never contain a quote. You could address this by writing more robust parsing code, but that’s more work—and this is only one example of the corner cases you may need to handle to accurately parse a family of log messages.
On the other hand, maybe your interest is not analyzing log message parameters, but rather removing them altogether. For data privacy or security reasons, you want sanitized log messages, where parameter values have been stripped out and replaced with generic placeholders. This way, “clean” log messages can be aggregated or retained long-term without the complications that accompany storing PII or other confidential information that may have found its way into error message parameters. While this may be the opposite of our first scenario (extracting message parameters for analysis purposes), implementing it still requires a technical means to differentiate between the base log message pattern (or template) and the parameters that have been filled into it. If you’re implementing this yourself, you’re looking at some form of log message parsing.
In either case, if only there was a way to avoid the effort and complexity associated with writing log message parsing code….
Introducing M’s Structured Error Messages
Meet M’s new structured error message capabilities!
M’s error functionality has recently been expanded to offer a new way of defining error messages, splitting message definition between a template and a list of parameter values. These components are preserved with first class representation in the error after it is raised, enabling error handling code (and, potentially by extension, external logging mechanisms and log analytics tools) to separately work with these components without the need for custom text parsing. This style of error message is known as a structured error message and is key to making structured logging possible.
The error seems to escape catching. When the expression is evaluated, Query Editor displays the error. Try loading the query’s output into Microsoft Power BI or Excel and the operation dies with an error. Clearly, there’s an error—but if you wrap the expression with a try, the error isn’t caught! To the contrary, the record output by try reports HasError = false, even though if you access that record’s Value field, Query Editor again shows the error.
What’s going on?! Have you discovered an uncatchable error? Is this a Power Query bug?
By default, a Dataverse Web API error message contains two fields, code and message. However, some errors have extra details available just for the asking.
Below, an OData annotations preference request is used to ask for additional error details. The request’s syntax is simple: include odata.include-annotations="{comma-separated list of requested annotations}" in the Prefer header.
GET {{webApiUrl}}SomethingThatCausesAnError
Prefer: odata.include-annotations="*"
(include the "always include" headers)
(Above, a wildcard is used to request all annotations. It may be best to only use a wildcard like this only temporarily, during development. Once the specific annotations of interest have been identified, change the request to be more specific. This avoids the cost of unnecessary extra data being assembled and sent across the wire.)
Be aware that error annotations are not necessarily intended for end-user or public consumption. The component raising the error may expect that these values will only be used for development and technical troubleshooting purposes and so may include content that is not end-user friendly or even that contains sensitive data. It is a good practice not to expose these extra error details publicly except when you are sure that the component raising the error will only return “safe to display” values.
Bonus Tip: If you’re writing a custom plugin, InvalidPluginExecutionException‘s first three arguments correspond to the OperationStatus, SubErrorCode and InnerError.Message annotations. Additionally, annotation TraceText will include any trace text written by the plugin, a fact that can be leveraged to include a full stack trace, if desired. Unfortunately, HelpLink is auto-set by Microsoft; I’m not aware of a way to set or override it.
Your Power Query is skipping merrily along its mashup way. Then, bam! Something bad happens! Uh oh! What do you do when an error raises its ugly head? Or, for that matter, what if code you write detects an anomaly and you want to announce this fact in an informative manner?
Thankfully, M has error handling capabilities, allowing you to both raise and handle runtime errors. We’ll learn how to do both.
Important: If you’re familiar with the idea of an exception from other programming languages, Power Query’s error handling is different in at least one significant respect from what you may be familiar with.
