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

Intermediate Power Query in Excel

Generating M code

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

Formula bar: Ch2_Formula_Bar.png

Advanced Editor: Ch2_Advanced_Editor.png

Intermediate Power Query in Excel

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

Ch2_Custom_Column_Ribbon_Screenshot.png

Intermediate Power Query in Excel

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"
Intermediate Power Query in Excel

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"
Intermediate Power Query in Excel

Advanced Indexing

Ch2_Before_Groupby_AllRows.png

  • Generate a custom index or rank based on defined groups
Intermediate Power Query in Excel

Advanced Indexing

Ch2_Before_Groupby_AllRows_SimpleIndex.png

  • Generate a custom index or rank based on defined groups
    • Simple Index
Intermediate Power Query in Excel

Advanced Indexing

Ch2_Before_Groupby_AllRows_GroupedIndex.png

  • Generate a custom index or rank based on defined groups
    • Advanced Index (within groups)
Intermediate Power Query in Excel

Advanced Indexing

Ch2_GroupBy_Aggregation.png

  • Generate a custom index or rank based on defined groups
  • Group By operation with "All Rows" aggregation
Intermediate Power Query in Excel

Advanced Indexing

Ch2_GroupBy_AllRows.png

  • 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
    
Intermediate Power Query in Excel

Let's practice!

Intermediate Power Query in Excel

Preparing Video For Download...