Question about M Code Processing

YouTube / Blogs / Other
1

@Melissa,

Really enjoyed your most recent video:

As always, your explanations were really clear and I understand now how to accomplish the topic task.

However, in watching it I realized that I don’t understand how Power BI processes M code. Unlike DAX, which processes roughly outside-in, I had assumed that M code processed sequentially top-to-bottom (like R code). However, that can’t be the case since in your video, you referenced the CurrentDate variable before you declared it, and that worked fine.

So, given that,what’s the order of processing of M code and what are the implications? (i.e., what HAS to be done before what?).

Thanks for clarifying.

  • Brian
2

Hi @BrianJ,

M code looks sequential because most of the time we built and reshape queries through the user interface. And we humans find it easier to understand when there’s that logical flow but as you already stated that is actually not required…

In M, an expression can reference previous expressions by name (identifier), and the evaluation process will automatically determine the order in which referenced expressions are calculated. List, Record, and Table expressions, as well as let expressions, are evaluated using lazy evaluation: they are evaluated when needed. All other expressions are evaluated using eager evaluation: they are evaluated immediately, when encountered during the evaluation process.

I hope this is helpful.

3

@Melissa,

Super helpful - thanks!

  • Brian
4

I know what you mean, this was a tricky idea to get through my brain, until I had to put together some if/then logic in my M Code statements. This was not as simple as if-this / then-that of a conditional column, but more complicated.

I’ve found a few posts on this over time, but I generally go back to this one from Avi Singh (since I was taking his course at the time): https://community.powerbi.com/t5/Community-Blog/Conditional-Code-Branching-in-Power-BI-Query-if-then-else-gt/ba-p/39998

image

Looking back at it now, it’s almost like using VAR in DAX, before VAR was available to us mere mortals. :slight_smile:

1 Like
5

@Heather,

Thanks – this is a great example for this discussion. In a weird way, the code you provided makes perfect sense to me. You declare your “variables” first and then call them at the end. What threw me for a loop in Melissa’s video, was that the variables were called before they were declared. However, based on her explanation above, I think that the IF statement block in your example could be put at the front of the code and still work, since the variables are based on table functions and would get evaluated as needed via the “lazy evaluation”. @Melissa - did I interpret that correctly?

  • Brian
6

Your a quick study @BrianJ :+1:
Experiment with this sample, you can alternate the Result by switching to the other “if” line

let
    IfNumIsOdd = Table.FirstN( IfNumIsEven, 1 ),
    IfNumIsEven = Table.AddColumn(
        Table.FromRecords({
            [OrderID = 1, CustomerID = 1, Item = "Fishing rod", Price = 100.0, Shipping = 10.00],
            [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0, Shipping = 15.00],
            [OrderID = 3, CustomerID = 2, Item = "Fishing net", Price = 25.0, Shipping = 10.00]
            }), "TotalPrice", each [Price] + [Shipping] ),
    Result = 
        //if Number.IsOdd( Date.DayOfWeek( Date.AddDays( DateTime.FixedLocalNow(), -1) ))
        if Number.IsOdd( Date.DayOfWeek( DateTime.FixedLocalNow() ))
        then IfNumIsEven
        else IfNumIsOdd
in
    Result
1 Like
7

Nah. I just have an excellent professor for this class.

  • Brian
8

Ain’t that the truth … we’re lucky to have such a great resource to help use … thanks @Melissa :clap:

2 Likes