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:

  1. Post report requests
  2. Wait for reports completion
  3. 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:

As we can imagine from ‘M’ evaluation rules, to get a” we need to calculate b”  first. We can suppose that before evaluation of value a” there goes value b” evaluation. So, we’ll get b” (current time), then extract current time 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:

Then it found b” and evaluate it:

The result of “b” is the local datetime with 2 seconds delay, so it is two seconds later than “a”. Of course, a – b approximately equals to -2 seconds.

It is easy to check:

There I changed evaluation order in the a” expression, so now b” 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 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:

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}:

Why there is a different result?

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:

The result will be {0, 0}:

Now it is expected – values didn’t recalculated

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.

Follow me: Facebooktwitterlinkedinmail