LAMBDA in reports
The report workbook can use LAMBDA, the same way Excel does, and you can combine it with the Q. functions, for example =Q.Samples(...).
Syntax
LAMBDA(parameter1, parameter2, …, calculation)
The calculation is the last argument. You can pass up to 253 parameters. Call it immediately, or use it where a function is expected:
=LAMBDA(x, x*2)(10) returns 20.
Inside the lambda, the parameter names are the only names the lambda adds. Batch fields are not automatically in scope. Read them with Q. functions in the calculation, or pass them in as arguments. Cell references and names that the workbook already knows (including Q.CurrentSample) can be used in the calculation.
With array functions
The engine also provides MAP, REDUCE, SCAN, MAKEARRAY, BYROW, and BYCOL.
| Formula | What it does |
|---|---|
MAP(array, LAMBDA(…)) |
One result per element. |
BYROW(array, LAMBDA(row)) |
One result per row. |
BYCOL(array, LAMBDA(column)) |
One result per column. |
REDUCE([initial], array, LAMBDA(accumulator, value)) |
Folds the array to one value. |
SCAN([initial], array, LAMBDA(accumulator, value)) |
Same fold, keeping each step. |
MAKEARRAY(rows, cols, LAMBDA(row, col)) |
Builds a calculated block. Row and column indexes start at 1. |
Example, sum of final concentrations for the current compound across sample keys:
=SUM(MAP(Q.SmpKeys, LAMBDA(k, Q.KeyCompound("Result.FConc", k, Q.CurrentCompound))))
Q.ByRowAndColumn is Quanto’s own cross-join. Both inputs must be cell references. The lambda receives the row value and the column value:
=Q.ByRowAndColumn(A1:D1, A2:A5, LAMBDA(r, c, r&"-"&c))
If those arguments are not references, the result is #REF!. If the third argument is not a lambda, the result is #CALC!.
ISOMITTED(parameter) is TRUE when an optional lambda argument was left out.
Nesting and errors
A lambda may call another lambda. A wrong parameter count, or a missing lambda where one is required, returns #VALUE! (“Incorrect Parameters”).
Q.VSample, Q.VCompound, Q.VCalibration, and Q.VQualifier are charts. They are not forwarded through a LAMBDA wrapper. Call them directly.
There is no separate Quanto function named FILTER beyond the field/operator/value triplets on Q.Samples, Q.Compounds, Q.AllCompounds, Q.Levels, and Q.AllLevels. Use those triplets, or MAP / BYROW with an IF, when you need a calculated column.