Conceptualizing M

As with any programming language, it is important to understand both the abstract principles of the language as well as more practical topics, such as what functions are available and how to use them. This chapter aims to provide you with a solid conceptual understanding of key, perhaps more abstract aspects of Power Query M, such as scope, the global environment, closure, and metadata. These concepts are crucial to truly becoming a master of Power Query M.

We will begin by delving into the concept of scope in Power Query M. Understanding scope is essential for controlling the visibility and accessibility of variables and functions within your queries. We will explore the distinction between local and global scope, examining how variables and functions interact within different scopes to produce effective data transformations.

Any discussion of scope naturally leads to a discussion of the global environment for Power Query M and how we can create our own...

Technical requirements

To follow along with this chapter, you will need to have Power BI Desktop installed.

Understanding scope

In Power Query M, scope determines where variables and functions can be accessed and used, and it plays a critical role in controlling the flow of data transformations and data manipulation operations:

• Global scope: Global scope refers to variables and functions that are defined at the query level, outside of any specific query step or function. Variables and functions with global scope can be accessed and utilized across all steps and functions within the entire query. They provide a way to store and share data or calculations between different parts of the query, ensuring consistency and reusability.
• Local scope: Local scope is the most common type of scope in Power Query M. It refers to variables and functions that are defined within a specific query step or a function definition. Variables and functions with local scope are only accessible within the step or function where they are defined. They cannot be referenced or used outside of their...

Examining the global environment

In Power Query M, the global environment refers to the top-level scope that encompasses the entire Power Query query. It represents the highest level of visibility and accessibility for variables and functions, making them available throughout the entire query’s code. The global environment serves as a container for global variables, functions, and settings that can be accessed and used across all query steps and functions.

When creating queries within Power Query Editor in Power BI Desktop, the global environment comprises the following three components:

• Standard library: A programming language’s standard library is a non-volatile collection of resources such as configuration data, documentation, classes, values, types, and pre-written code. M’s standard library consists of the following elements:
  • Built-in functions
  • Built-in types
  • Built-in enumerations
• Shared extension...

Understanding closures

In programming languages, a closure is a powerful concept that allows a function to capture and retain references to variables from its lexical environment (the environment where the function is defined). This means that even after the outer function has finished executing or has gone out of scope, the inner function (the closure) still retains access to the variables from its enclosing scope.

Closures are created when an inner function references variables from its containing function or any other surrounding scope. The inner function closes over those variables, hence the term closure.

The ability of a closure to maintain access to variables from its lexical environment is particularly useful in scenarios where you need to create functions with behavior that depends on the values of certain variables at the time the function was defined.

Here’s a simple example of a closure in Power Query M:

let
  x = 10,
  closureFunction = () =>...

Managing metadata

Simply put, metadata is data about data. In Power Query M, metadata can be associated with any value using the meta keyword to define a metadata record. Metadata in and of itself does not do anything, nor does it change the behavior of a value in any way.

Consider the following query:

let
    x = 10 meta [Type = "Whole number", OoM = 1],
    y = 20 meta [Type = "Whole number", OoM = 1],
    z = 30 meta [Type = "Whole number", OoM = 1]
in
    x * y * z

This query still returns 6000, just as before. However, the metadata associated with each value can be accessed and returned by using the Value.Metadata function, as follows:

let
    x = 10 meta [Type = "Whole Number", OoM = 1],
    y = 20 meta [Type = "Whole Number", OoM = 1],
    z = 30 meta [Type = "Whole Number", OoM = 1]
in
    Value.Metadata(x)[Type]

This query returns Whole Number.

Two additional metadata functions exist, Value...

Summary

In this chapter, we covered some important but perhaps rather more obscure and abstract aspects of the M language, including scope, global environments, closures, and metadata. We also briefly explored sections as well as creating our own global environments using the Expression.Evaluate function. Sections are covered in depth in Chapter 16, Enabling Extensions.

While the information covered in this chapter might appear to be less practical than that of other chapters, the concepts conveyed within this chapter are critical to truly understanding the M language and fully leveraging its potential.

In the next chapter, we start to put the abstract concepts from this chapter to practical use by working with nested structures, where the concept of scope is vitally important.

Learn more on Discord

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

https://discord.gg/vCSG5GBbyS