As we learned last time, normally, M code is evaluated in a global identifier resolution scope consisting of all shared members + the standard library. Also, normally, we can’t inject additional identifiers into this global environment. Normally isn’t always. Today, we learn about the exception: where both of these normalities do not apply.
That’s not all: Did you know that M has a mechanism for remembering how to access variables that later go out of scope? Closures open up powerful options, particularly when generating functions…and even enable building an object-like programmatic construct that maintains internal private state and is interacted with through a public interface (kind-of, sort-of somewhat like an object from object-oriented programming!).
The same identifier name (think: variable name, field name, etc.) can be defined more than once in the same set of Power Query expressions. If you reference an identifier name that’s been defined in multiple places, which of those definitions will your reference point to?
In this post, let’s learn how M sorts this out. We’ll also explore sections—the usually hidden “frame” at the core of organizing the different expressions that make up a Power Query program.
Let’s get to it…and have fun while we’re at it!
Up until now, we’ve used variables without talking much about their specifics. Since we’re operating under the assumption that you have at least a little experience with another programming or scripting language, how we’ve used variables has probably made sense.
However, there might be differences—perhaps even significant differences—between how variables work in other languages you’ve used with and how they behave in the Power Query M language. Let’s spend some time exploring variables and related concepts to clear up any lurking confusion and position you to take full advantage of what M offers.
We’ll start with a brief recap of the main unit where we define variables: the
let expression. Then, we’ll talk about how variables (and other things) are identified. Related to identifiers is scope, so we’ll cover that, too. Next time, we’ll expand our understanding of how variables work by learning about M’s paradigm.
Just recently, I realized that T-SQL allows variables to be declared and set with a single statement:
DECLARE @UserID int = 1592;
Up until this discovery, I only knew about the more traditional, two-step declaration and assignment syntax:
DECLARE @UserID int;
SET @UserID = 1592;