Custom columns

Intermediate Power Query in Excel

Lyndsay Girard

Performance Analytics Consultant

M Formula Language

'M' for short (stands for Data Mashup)
Functional programming language in Power Query
Case-sensitive
Wide range of built-in functions

Programming on laptop

Generating M code

Behind the scenes of every applied query step
- Automatically written
M code viewable:
- Formula bar
- Advanced Editor

Formula bar:

Advanced Editor:

Custom Columns

User-defined calculated column
Written in M language
Can extend the functionality of built-in transformations.
- Nested conditional logic
- Advanced indexing
- Complex calculations

Nested Conditional Logic

Referencing columns and values
Can involve multiple levels of conditional statements
- If... Then... Else statements
Can combine with logical operators
- AND
- OR

Basic Conditional Logic

if age >= 65  
    and arrivalmode = "Car"
    then "group1"
else "group2"

Nested Conditional Logic

Referencing columns and values
Can involve multiple levels of conditional statements
- If... Then... Else statements
Can combine with logical operators
- AND
- OR

Custom Conditional Logic

if age >= 65 and age <= 80
    and arrivalmode = "Car" 
   then "group1" 
    else if age >= 65 and age <= 80
    and arrivalmode = "ambulance" 
   then "group1a"
else "group2"

Advanced Indexing

Generate a custom index or rank based on defined groups

Advanced Indexing

Generate a custom index or rank based on defined groups
- Simple Index

Advanced Indexing

Generate a custom index or rank based on defined groups
- Advanced Index (within groups)

Advanced Indexing

Generate a custom index or rank based on defined groups
Group By operation with "All Rows" aggregation

Advanced Indexing

Generate a custom index or rank based on defined groups
Group By operation with "All Rows" aggregation
Combine with custom M table function
```
Table.AddIndexColumn
```

Let's practice!

Intermediate Power Query in Excel

Preparing Video For Download...