1. M Formula Language
Welcome back! In this chapter we are going to delve deeper into M formula language, the advanced editor, as well as introduce parameters and custom functions to streamline your analytical processes.
2. Advanced Editor
M code is the case-sensitive formula language used behind the scenes of every query in the Power Query editor.
The M code in the advanced editor is always structured by what we call the "let" expression, which has two parts: a "let" clause, and an "in" clause.
After the "let" clause comes a comma-separated list of named variables or expression steps. These names are what we see in the applied steps pane.
Expression steps build on their previous step by referring to the previous step name.
The final returned output is what follows the "in" clause.
Let's walk through an example. Here's an empty query Let expression.
3. Advanced Editor
This "let" expression defines a table based on two entered records.
The "let" defines "MyTable" using a Table function, followed by function input. In this case, two records are listed, each containing three columns (ID, Name, and Result) and their values.
The "in" clause lists the only named step, "MyTable" so this is what will be returned in the query.
4. Advanced Editor
Here is the same "Let" expression with another step added after the initial "MyTable" step. We can see that it is separated by a comma.
This second step, "Sorted Rows", is a Table.Sort function, to sort MyTable by Result in Ascending order.
The named step "Sorted Rows" is what the query returns since it is shown after the "in" expression.
5. Advanced Editor
Adding another transformation step, we can see how new steps refers to previous step names.
In this case, the lower step's function references the previous sort step, and the lower step is returned since it follows the "in" statement.
6. Value types
It is helpful to understand the type system for M, which consists both primitive and structured types.
Primitive types are single-part values, such as number, logical, text, or null. In this case, the numeric 1 and the text "A" would be primitive values.
Structured types primarily include lists, records, tables and functions, and can contain one or more types of primitive values.
7. Structured value types
Let's explore some structured value types a bit further.
Lists are structured by only one column enclosed in curly braces. Records, define a single row with multiple columns in square brackets, and Tables can have multiple rows and columns.
8. M Built-in Functions
A function is essentially a value that, when invoked with arguments, produces a new value.
Since M code is a functional language, it’s all about the functions, and M code comes with a large library of over 700 functions!
Some common function types are listed here. Many of these functions are invoked automatically when using Guided User steps in the Power Query Editor, or can be used to build more complex custom columns as we did in Chapter 2 with the Grouped Index example.
9. Custom Functions
Sometimes, custom functions are required. This may be when more complex calculations are required, for adding reusability to a repetitive task, invoking parameter input, or seeking to optimize performance of a query.
Here is the general structure of a custom function. First are the parameters in parentheses, followed by the goes-to symbol '=>'(an equal sign followed by a greater than sign), then the expression defining the function.
10. Custom Functions
For example, this simple function “MyFunction” defined here has one parameter (x) and adds 1.
Providing the input of 10 to this custom function returns the value of 11.
11. Query Parameters
Query parameters are extremely useful placeholders, enabling us to dynamically pass values to a query.
This enhances use of queries, making them more flexible and reusable.
A common example is for filtering queries based on parameter input, rather than manual filtering or hard-coded input.
Custom functions can also pass query parameter input, enhancing flexibility to their use cases.
12. Let's practice!
Let's practice!