Module 17
Functions, each and scope
each, custom functions, branching and error handling in M.
You'll be able to
- Explain each and _
- Write a custom function
- Branch with if … then … else
Lessons
- 1.Write and use custom functionsDefine a function, give it typed parameters and call it from a custom column.
- 2.Row context: whole-table values and the previous rowCombine the current row with values from other rows in a custom column.
- 3.Raise and handle errors in your own functionsWrite functions that reject bad input with clear messages that callers can handle.
- 4.Scope, closures and List.AccumulateUse closures and accumulation to express rules that table steps can't.
Take the functions, each and scope quiz (8 questions)
Exercises
- advanced~12 minPro
Write a shipping-cost function and apply it (kite shop)
Define a function that prices shipping from quantity and region, then add a Shipping column with it.
- advanced~12 minPro
Write a shipping-cost function and apply it (bakery)
Define a function that prices shipping from quantity and region, then add a Shipping column with it.
- advanced~12 minPro
Write a shipping-cost function and apply it (bike rental)
Define a function that prices shipping from quantity and region, then add a Shipping column with it.
- intermediate~9 minPro
Write a tax function with an optional rate (shop)
Define Gross(net, optional rate) that uses 20% when no rate is given, and apply it to every order.
- intermediate~9 minPro
Write a tax function with an optional rate (catering)
Define Gross(net, optional rate) that uses 20% when no rate is given, and apply it to every order.
- intermediate~9 minPro
Write a tax function with an optional rate (books)
Define Gross(net, optional rate) that uses 20% when no rate is given, and apply it to every order.
- advanced~12 minPro
Add a running total in date order (consultancy)
Sort claims by date, then add a Running Total of Amount from the first claim to each row.
- advanced~12 minPro
Add a running total in date order (school)
Sort claims by date, then add a Running Total of Amount from the first claim to each row.
- advanced~10 minPro
Trim every column except Amount with one generated step (members)
Text columns are padded with spaces, and new ones keep appearing. Build the Table.TransformColumns list from the column names.
- advanced~10 minPro
Trim every column except Amount with one generated step (suppliers)
Text columns are padded with spaces, and new ones keep appearing. Build the Table.TransformColumns list from the column names.
- advanced~11 minPro
Label orders with a price band from a band table (kite shop)
Bands are defined in a table (Band, Min, Max). Write a function that finds a value's band and use it on every order.
- advanced~11 minPro
Label orders with a price band from a band table (bakery)
Bands are defined in a table (Band, Min, Max). Write a function that finds a value's band and use it on every order.
- advanced~11 minPro
Label orders with a price band from a band table (bike rental)
Bands are defined in a table (Band, Min, Max). Write a function that finds a value's band and use it on every order.
- advanced~10 minPro
Fix a nested each that compares a row with itself (kite shop)
Every order gets the same Line Qty: an inner each compares the line's OrderID with itself. Name the parameters.
- advanced~10 minPro
Fix a nested each that compares a row with itself (bakery)
Every order gets the same Line Qty: an inner each compares the line's OrderID with itself. Name the parameters.
- advanced~10 minPro
Fix a nested each that compares a row with itself (bike rental)
Every order gets the same Line Qty: an inner each compares the line's OrderID with itself. Name the parameters.
- advanced~14 minPro
Count each employee's levels of management with recursion (logistics)
Each employee row names its manager (or null at the top). Compute how many managers sit above each person.
- advanced~14 minPro
Count each employee's levels of management with recursion (software)
Each employee row names its manager (or null at the top). Compute how many managers sit above each person.
- advanced~14 minPro
Count each employee's levels of management with recursion (hospital)
Each employee row names its manager (or null at the top). Compute how many managers sit above each person.
- intermediate~8 minPro
Count answered questions using the whole row (staff survey)
Each row is one survey response with a column per question. Count how many questions each respondent answered.
- intermediate~8 minPro
Count answered questions using the whole row (visitor feedback)
Each row is one survey response with a column per question. Count how many questions each respondent answered.