M language basics

As previously noted, M is a powerful language designed for data ingest and transformation within a variety of Microsoft software and services. Understanding the basics of the M language is essential for effectively leveraging its capabilities.

Here are some important fundamentals regarding the M language:

• Expressions and functions: In M, expressions form the building blocks of data transformations. An expression represents a computation or operation that evaluates to a value. M provides a wide range of built-in functions that can be used to perform operations on data. Functions in M are called using a syntax where the function name is followed by arguments within parentheses. For example, the function Text.Start("Hello, World!", 5) returns the substring Hello from the input text. More about expressions and functions are covered in Chapter 4, Understanding Values and Expressions, as well as Chapter 9, Parameters and Custom Functions.
• Data types: M supports various data types, including text, numbers, dates, times, lists, tables, and records. Understanding the data types in M is crucial for performing accurate transformations. M provides functions to convert between different data types and manipulate data, based on their inherent characteristics. For example, the Text.From function converts a value to text, while the Date.Year function extracts the year component from a date or datetime value. Data types are covered in Chapter 5, Understanding Data Types.
• Variables and constants: M allows you to define variables and constants to store and reuse values during data transformations. Variables are created within a let expression, followed by a comma-separated list of variable assignments. Constants, on the other hand, are fixed values that remain constant throughout the execution. Variables and constants help improve code readability, enable reuse, and make complex transformations more manageable. More about variables and constants can be found throughout this book.
• Operators: M supports a variety of operators to perform mathematical calculations, logical comparisons, and text manipulations. Arithmetic operators (+, -, *, /, and so on) are used for numeric calculations, while comparison operators (>, <, =, and so on) evaluate logical conditions. The combination operator ‘&' is used for concatenating text values, appending lists and tables, or merging records. Operators are covered in Chapter 4, Understanding Values and Expressions.
• Step-by-step transformation process: M follows a step-by-step transformation process where each step defines a data transformation operation. The Power Query editor provides a visual interface to define these steps and generates the corresponding M code. Steps can include operations such as filtering rows, removing duplicates, splitting columns, merging tables, and aggregating data. Chapter 2, Working with Power Query/M, covers this topic in more detail.
• Query folding: Query folding is an optimization technique in Power Query that pushes data transformations to the data source whenever possible. When using M, it is important to be aware of query folding to ensure efficient data processing. Query folding can improve performance by reducing data transfer between the data source and Power Query. However, not all transformations can be folded, so it is essential to understand which operations can be folded and which cannot. For example, when using Direct Query or Dual storage mode for tables, all M queries must fold, which can limit certain transformation operations. Query folding is discussed in Chapter 7, Conceptualizing M, and in Chapter 15, Optimizing Performance.
• Error handling and debugging: M provides error handling mechanisms to catch and handle exceptions during data transformations. By using functions like try, otherwise, and error, you can control the flow of execution and handle potential errors gracefully. Additionally, M supports debugging capabilities, such as the ability to step through the code to identify and resolve issues in complex transformations. Error handling and debugging are covered in Chapter 12, Handling Errors and Debugging.
• Case sensitivity: M is case-sensitive. This applies to all functions, expressions, variables, constants, and other aspects of the M language.
• Commenting: Comments in M follow the C language commenting style. Inline comments are proceeded by double slashes ( // ) while block comments use the slash-asterisk/asterisk-slash pattern ( /* and */ ).

Now that we have a good understanding of the core components of the M language, let’s next explore the most fundamental component of the M language, the let expression.

The let expression

At the core of the M language is the let expression, which must be paired with an in expression. In simple terms, the let expression contains the input and transformations, while the in expression contains the output. A simple Hello World for M looks like the following:

let
    Hello = "Hello World"
in
    Hello

This code would return the ubiquitous Hello World text.

It is important to note that every expression within a let statement must be followed by a comma ( , ) except the last expression prior to the in expression. Thus, if the let expression consists of multiple sub-expressions, then the code might look like the following:

let
    Hello = "Hello",
    World = "World",
    Return = Hello & " " & World
in
    Return

This code also returns Hello World as output.

Understanding the basics of M, including expressions, functions, data types, variables, operators, and the step-by-step transformation process, is vital for effectively manipulating and preparing data. By mastering these foundational concepts, you gain the ability to perform complex transformations, optimize data workflows, and unlock the full potential of the M language. The rest of this book is devoted to helping you master all of these foundational concepts and how to apply them to complex data transformations.