M Formula Language

Intermediate Power Query in Excel

Lyndsay Girard

Performance Analytics Consultant

Advanced Editor

Written in M formula language (M code)
- Case-sensitive
Let expressions have two parts:
- "let" and "in" clause
- Comma-separated list of variable names or step identifiers.
  - Each step builds upon previous step by referring to its name
- Step listed after the "in" clause is the visible query output

let
    Source = ""
in
    Source

Advanced Editor

let
    MyTable = Table.FromRecords({
    [ID = 1, Name = "A", Result = 10],
    [ID = 2, Name = "B", Result = 5]})
in
    MyTable

Let expression with 1 step

Advanced Editor

let
    MyTable = Table.FromRecords({
    [ID = 1, Name = "A", Result = 10],
    [ID = 2, Name = "B", Result = 5]}),
    #"Sorted Rows" = Table.Sort(MyTable,{{"Result", Order.Ascending}})
in
    #"Sorted Rows"

Let expression with two steps

Advanced Editor

let
    MyTable = Table.FromRecords({
    [ID = 1, Name = "A", Result = 10],
    [ID = 2, Name = "B", Result = 5]}),
    #"Sort" = Table.Sort(MyTable,{{"Result", Order.Ascending}}),
    #"Lower" = Table.TransformColumns(#"Sort",{{"Name", Text.Lower, type text}})
in
    #"Lower"

Let expression with three steps

Value types

Primitive

Single-part value (e.g. number, logical, text, or null)
```
[ID = 1, Name = "A"]
```

Structured

List
Record
Table
Function

Structured value types

List:

Zero-based ordered sequence of values with one column
```
List = {1,2,3}
```

Record:

A structure with a single row, but multiple columns
```
Record = [Column1=1,Column2=2]
```

Table:

Combination of multiple rows and multiple columns

Table = #table({"Column A","Column B"},
      {{1,10},{2,20}})

M Built-in Functions

Function:

Value that, when invoked with arguments, produces a new value.

Table functions
List functions
Number functions
Record functions
Date functions
Time functions
Duration functions
... many more!

¹ https://learn.microsoft.com/en-us/powerquery-m/power-query-m-function-reference

Custom Functions

Complex calculations
Reusability
Parameterization
Performance optimization

Custom function syntax:

= (Variable as Data Type, Variable as Data Type) => (Output Expression)

Custom Functions

Simple custom function example:

let
    MyFunction = (x) => x + 1
in
    MyFunction(10)

Query Parameters

Placeholders to dynamically pass values to a query
- Make queries more flexible and reusable
  - Dynamic filters
  - Custom functions

Hand adjusting slider buttons, changing application settings on the screen. Customizing user interface with toggle switch

Let's practice!

Intermediate Power Query in Excel