### On lazy value evaluation order in Power Query and Power BI

‘M’ (a Power Query Formula language) is a very powerful and interesting, but sometimes it could be a little confusing because of its lazy behavior.

Yesterday a friend of mine asked me for help with custom API connector in Power Query / Power BI. This connector has to work as follows:

- Post report requests
- Wait for reports completion
- Download reports

On the step 2 my friend used Function.InvokeAfter() built-in Power Query function, which supposed to pause code for a few seconds. But his code didn’t worked as expected – It looks like there are no requests posted at all.

We’ve found a solution to his task, but to make a long story short, there is what I have found from this case.

Let us look at this short code:

1 2 3 4 5 |
let a = DateTimeZone.LocalNow() - b, b = Function.InvokeAfter(DateTimeZone.LocalNow, #duration(0,0,0,2)) in a |

As we can imagine from ‘M’ evaluation rules, to get **“**** a”** we need to calculate

**“**

**first. We can suppose that before evaluation of value**

*b”*

*“***a”**there goes value

**“**

**evaluation. So, we’ll get**

*b”***“**

**(current time), then extract**

*b”**current tim*e from the same

*current time*and get zero.

No way. The result is about -2 seconds: **-00:00:02.0183210**

Why? It is not so obvious, but there is a very easy answer: ‘M’ evaluates a value from left to right. When ‘M’ check for the a expression, it calculate first part:

1 |
DateTimeZone.LocalNow() |

Then it found *“*** b”** and evaluate it:

1 |
b = Function.InvokeAfter(DateTimeZone.LocalNow, #duration(0,0,0,2)) |

The result of ** “b”** is the local datetime with 2 seconds delay, so it is two seconds later than

**. Of course,**

*“a”***approximately equals to**

*a – b**-2*seconds.

It is easy to check:

1 2 3 4 5 |
let a = b - DateTimeZone.LocalNow(), b = Function.InvokeAfter(DateTimeZone.LocalNow, #duration(0,0,0,2)) in a |

There I changed evaluation order in the **“**** a”** expression, so now

**“**

**is evaluated first, then second part ( DateTimeZone.LocalNow() ) is evaluated. As this evaluation is very quick, we have no delay and got the same time as in**

*b”***“**

**. The new result is**

*b”**0*, zero.

So, what I have found here is that relatively complex expressions in ‘M’ evaluates from left to right.

There is another interesting result. Let us see the next code:

1 2 3 4 5 |
let a = DateTimeZone.LocalNow(), b = Function.InvokeAfter(DateTimeZone.LocalNow, #duration(0,0,0,2)) in { Duration.Seconds(a-b), Duration.Seconds(b-a) } |

The result of this code should be a list with two values. What I expected from previous example? There should be something like *{-2, 0}*, like results of previous calculations.

No way. The result is *{-2, 2}*:

The reason is the lazyness of ‘M’: when the first element of the list evaluated, then it works like in example above: evaluate **“a”** then evaluate **“b”** (plus 2 sec), extract **“b”** from **“a”**. But for the second element ‘M’ did not make any evaluations of **“a”** or **“b”**. They already evaluated, so, as **“b”** is bigger than **“a”** for 2 seconds, extraction gives me *+2*.

If I change the order of the list elements:

1 2 3 4 5 |
let a = DateTimeZone.LocalNow(), b = Function.InvokeAfter(DateTimeZone.LocalNow, #duration(0,0,0,2)) in {Duration.Seconds(b-a), Duration.Seconds(a-b)} |

The result will be *{0, 0}*:

Now I can easily explain why: when evaluating the first element, * b-a*, the

**“b”**evaluated first, then

**“a”**immediately evaluated and it equals to

**“b”**, and we get a

*zero*as the result of extraction. Already calculated

**“a”**and

**“b”**then swap their places and give us the same result.

It looks as a very interesting finding for me. I think I have to keep it in mind when I’ll try to implement some time-delayed evaluations in queries.