Dynamic, Lazy Records

, ,

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.

2 thoughts on “Dynamic, Lazy Records

  1. Alan

    One can observe the difference easily like so:

    Quick = Record.AddField([X=0], "Y", () => Function.InvokeAfter(() => 1, #duration(0,0,0,10)), true)[X]
    Slow = Record.AddField([X=0], "Y", Function.InvokeAfter(() => 1, #duration(0,0,0,10)))[X]
    

    Quick is slow if you inspect the record in the query editor, but if you access the X field the Y field is never evaluated so it returns 0 immediately.

    However,

    Slow1 = Record.AddField(
     Record.AddField([X=0], "Y", Function.InvokeAfter(() => 1, #duration(0,0,0,10)))
    , "Z", () => Function.InvokeAfter(() => 2, #duration(0,0,0,20)))[X]
    Slow2 = (Record.AddField([X=0], "Y", Function.InvokeAfter(() => 1, #duration(0,0,0,10))) & Record.AddField([]
    , "Z", () => Function.InvokeAfter(() => 2, #duration(0,0,0,20))))[X]
    

    both return after 10 seconds.

    Is there no way to programmatically create a record with multiple delayed fields?

    Reply
    1. Ben Gribaudo Post author

      In order to programmatically create delayed fields, Record.AddField‘s optional last argument must be set to true. 🙂 The above Slow1 and Slow2 examples don’t do this, so the values for the fields they programmatically add are computed immediately (but after the specified 10 seconds elapses) vs. only if and when actually needed.

      Does the below help?

      let
          BaseRecord = [X=0],
          AddY = Record.AddField(BaseRecord, "Y", () => Function.InvokeAfter(() => 1, #duration(0,0,0,10)), true),
          AddZ = Record.AddField(AddY, "Z", () => Function.InvokeAfter(() => 1, #duration(0,0,0,10)), true)
      in
          AddZ[X] // returns zero instantly
      
      Reply

Leave a Reply

Your email address will not be published.