In the below record, when is Amount‘s value calculated?
[ FieldA = …, Amount = ExpensiveToCompute("some", "arguments"), … ]
Only if needed. Why? Power Query’s record field values are lazily evaluated. A field’s expression is only evaluated if its value is needed. If it’s not, the cost of computing the value isn’t expended. Nice!
Let’s say, instead, you’d like to dynamically add Amount to an existing record. Is something like the following effectively equivalent to the above?
let SomeExistingRecord = [ FieldA = …, … ] in Record.AddField( SomeExistingRecord, "Amount", ExpensiveToCompute("some", "arguments") )
No! Whoa! Amount‘s laziness went good bye! Above,
ExpensiveToCompute("some", "arguments") is executed whether or not Amount‘s value is ever needed.
Why? In Power Query, the arguments passed to a function are eagerly evaluated—that is, their values are computed before the function is invoked. Above, before
Record.AddField runs, M’s engine invokes ExpensiveToCompute because it needs to pass the value returned from that function as
Record.AddField‘s third argument. So, ExpensiveToCompute is always computed, whether or not record field Amount‘s value is ever needed.
Make It Lazy
Is there a way to programmatically add a field to a record with its value being lazily evaluated?
Turns out, there is.
Record.AddField‘s optional fourth argument, delayed, is the key. Set this to true and then, instead of passing the value for the field as
Record.AddField‘s third argument, pass a zero-argument function which, when invoked, returns the value for the field.
Record.AddField( SomeExistingRecord, "Amount", () => ExpensiveToCompute("some", "arguments"), true )
Record.AddField‘s arguments are still eagerly evaluated (as M always computes a function’s argument values before invoking the function). However, the third argument is no longer defined as “the value produced by invoking ExpensiveToCompute” but rather as “the value produced by defining a function that invokes ExpensiveToCompute.”
With this change, when M eagerly evaluates
Record.AddField‘s arguments, all it evaluates is the definition of that zero-argument function. This produces something called a function value. The function hasn’t been executed; rather, the function value is a kind of “handle” that can later be used to invoke the actual function, if and when desired. This “function value” is then held, and—can you guess where this is going?—will only be involved to get the actual value for record field Amount if that value is needed.
So there you have it: The ability to dynamically build records by programmatically adding fields whose values are lazily evaluated.