An Error’s Expression Stack: A “Journal” of the Locations It Propagates Through

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. 🙂

A Stack of Locations

The expression stack, when present, is found as a list in the error’s metadata field Expression.Stack.

let
    Data = {
        error "bad"
    },
    Result = try List.Sum(Data),
    ErrorMetadata = Value.Metadata(Result[Error])
in
    ErrorMetadata

The list’s entries are given in the order that propagation occurred. So, the first list item corresponds with the first journaled location that the error propagated through, and the last item identifies the last journaled location the error propagated out of.

Each record is in the form of:

[
	Function = (value)
	Location = [
		Section = (section name),
		Start = [
			Row = (number),
			Column = (number)
		],
		End = [
			Row = (number),
			Column = (number)
		]
	]
]

For example:

[
	Function = null,
	Location = [
		Section = "Section1",
		Start = [
			Row = 4,
			Column = 8
		],
		End = [
			Row = 4,
			Column = 19
		]
	]
]

The row and column numbers correspond with the location of the particular piece of code in the section document that contains that code. Locations are given using 0-based indexing (e.g. row 1 = line 2 in the section document).

For viewing friendliness, expression stacks can be rendered out in flattened form using a helper method like the following:

(errorDetails as record) as table =>
let 
    Stack = Value.Metadata(errorDetails)[Expression.Stack],
    AsTable = Table.FromRecords(Stack),
    ExpandedLocation = Table.ExpandRecordColumn(AsTable, "Location", {"Section", "Start", "End"}, {"Location.Section", "Location.Start", "Location.End"}),
    ExpandedLocationStart = Table.ExpandRecordColumn(ExpandedLocation, "Location.Start", {"Row", "Column"}, {"Location.Start.Row", "Location.Start.Column"}),
    ExpandedLocationEnd = Table.ExpandRecordColumn(ExpandedLocationStart, "Location.End", {"Row", "Column"}, {"Location.End.Row", "Location.End.Column"}),
    Typed = Table.TransformColumnTypes(ExpandedLocationEnd, {{"Location.Section", type text}, {"Location.Start.Row", Int64.Type}, {"Location.Start.Column", Int64.Type}, {"Location.End.Row", Int64.Type}, {"Location.End.Column", Int64.Type}})
in
    Typed

An example of using the above, assuming that it was named RenderExpressionStack:

let
    Data = {
        error "bad"
    },
    Result = try List.Sum(Data)
in
    RenderExpressionStack(Result[Error])

If we extract the section document (perhaps by using Data Mashup Explorer, if we’re using Power Query in Microsoft Excel; something not so easy to do when using Microsoft Power BI Desktop), we can see how the above-reported locations identify where the relevant expressions are located in source code, enabling us to follow the error’s path of propagation.

Conclusion

Interesting? Yes! Practical to use? Maybe once in a while, but this technique’s usefulness is limited by the effort involved with accessing, then mapping locations to, the section document.