Power Query M Primer (Part 14): Control Structure

Nope. That’s not a typo in the title. In the Power Query world, there aren’t control structures (plural); there’s just one control structure (singular). We’re about to examine its simplicity. As to the “missing” control structures (which you may be used to from other programming languages), we’ll explore ways of implementing similar functionality the M way.

Here we go!

Series Index

• Introduction, Simple Expressions & let (part 1)
• Functions: Defining (part 2)
• Functions: Function Values, Passing, Returning, Defining Inline, Recursion (part 3)
• Variables & Identifiers (part 4)
• Paradigm (part 5)
• Types—Intro & Text (Strings) (part 6)
• Types—Numbers (part 7)
• Types—The Temporal Family (part 8)
• Types—Logical, Null, Binary (part 9)
• Types—List, Record (part 10)
• Tables—Syntax (part 11)
• Tables—Table Think I (part 12)
• Tables—Table Think II (part 13)
• Control Structure (part 14) [this post]
• Error Handling (part 15)
• Type System I – Basics (part 16)
• Type System II – Facets (part 17)
• Type System III – Custom Types (part 18)
• Type System IV – Ascription, Conformance and Equality’s “Strange” Behaviors (part 19)
• Metadata (part 20)
• Identifier Scope & Sections (part 21)
• Identifier Scope II – Controlling the Global Environment, Closures (part 22)
• Query Folding I (part 23)
• Query Folding II (part 24)
• Extending the Global Environment (part 25)
• More to come!

Conditional Expressions – if

What if you want one or the other of two expressions to be executed, with the choice of which based on some condition? Power Query’s if expression allows you to do just this!

if TestExpression
then ExpressionToUseWhenTestEvaluatesToTrue
else ExpressionToUseWhenTestEvaluatesToFalse

When evaluated, the value of the test expression is first computed. If the result is a logical true, the then expression is evaluated and its value returned. If the test produces false, the else expression is evaluated and its value returned. If test evaluates to something other than a logical value (true or false), an error is raised.

Pretend we have a table of test scores. There’s a need to add a column indicating whether each score is a pass or a fail. A conditional expression comes to the rescue:

let
  Source = GetTestScoresFromSource(),
  EvaluateScore = (score) => if score >= 0.7 then "Pass" else "Fail",
  Result = Table.AddColumn(Source, "Result", each EvaluateScore([Score]))
in
  Result

Some programming languages allow an if expression without an else clause. This isn’t allowed in Power Query. In M, every expression (including if expressions) must either return a value or raise an error. Allowing if without else would result in nothing happening when the if test evaluates to false, which would break how M works.

Power Query has exactly one general syntax form for if. Unlike some other languages, there’s no short-form general purpose syntax variation (so no alternative syntax like (score >= 0.85)? "Pass" : "Fail"). However, there is a shortcut syntax (well, technically, an operator) for null coalescing.

Suppose you want to return a value so long as it is not null; but if it is null, you instead want to return an alternate value. You could code this out in long form using an if statement—or more succinctly using the null coalescing operator (??). Both achieve the same effect of returning ValueA unless it is null, in which case ValueB is returned.

if ValueA <> null then ValueA else ValueB
ValueA ?? ValueB

(The null coalescing operator is a new addition to Power Query. It has not been officially documented yet and may not be available in all environments.)

Speaking of syntax, there’s also no special elseif syntax. Instead, to apply another conditional expression when the first condition fails, simply put an if statement in the proceeding statement’s else clause. Repeat this as many times as necessary to achieve the desired effect.

(Grade) => 
if Grade= "A" then 0.9
else if Grade= "B" then 0.8
else if Grade= "C" then 0.7
else if Grade= "D" then 0.6
else 0 // Grade= "F"

Chaining a more than a few if statements together like this can get verbose and violate the Don’t Repeat Yourself (DRY) principle. If you’re familiar with other programming languages, you might want to replace a set of if ... else ifs with a switch statement. M doesn’t provide a switch-like syntax option. Instead, perhaps you can treat the set of conditionals as a set of lookup values, placing them in a table or record then looking up the value of interest.

For example, using a table:

(Grade) => 
let
  Map = #table({"LetterGrade", "Score"}, {{"A", 0.9}, {"B", 0.8}, {"C", 0.7}, {"D", 0.6}, {"F", 0}})
in
  Map{[LetterGrade = Grade]}[Score]

Or, in this case, even more concisely, using a record:

(Grade) => Record.Field([A = 0.9, B = 0.8 , C = 0.7, D = 0.6, F = 0], Grade)
 
// alternately -- treats 0 as the default value to return if Grade does not match a field name in the record
(Grade) => Record.FieldOrDefault([A = 0.9, B = 0.8 , C = 0.7, D = 0.6], Grade, 0)

Looping Constructs?

As far as control structures go, if is it. There are no other control structures, including no language syntax for looping constructs like foreach, for or while/do while (which you might be familiar with from other programming languages).

You might find this surprising and perhaps be inclined to think that M is a very immature language to be missing such fundamental concepts. Not so fast! It just might be that functional programming in general and/or M in particular are different from what you’re used to and so rely on different means to achieve the same ultimate effect.

Let’s explore some of those different approaches….

foreach

Traditionally, a foreach expression executes a block of code for each item in a collection (like each item in a list or each row in a table). Power Query is built to process list items and table rows. However, instead of explicitly coding low-level looping details, you declaratively use library functions to define what you want done at a higher level, and let the language and library take care of applying that processing to the relevant items or rows.

For example, to apply a transform to each value in a column, it’s not necessary write a foreach which loops through the table’s rows one at a time, updating the column of interest in each row as it’s iterated over. Instead, you use Table.TransformColumns to say “apply the given transform to the specified column” and M takes care of the menial, row-by-row application of that transformation. Similarly, to add a new column, you don’t iterate through the table’s rows, adding the column to each row one at a time. Instead, you simply declare your intention using Table.AddColumn, providing that method with an expression defining the new column, and M takes care of the per-row application.

Since your focus is declaring intent vs. coding up row-by-row processing to implement your intent, the resulting syntax may end up more succinct. For example, instead of imperatively using foreach to sum up values in a column, in the M world you’d simply declare that intent by applying List.Sum to the column.

// Instead of this:
var total = 0;
foreach(var row in SomeTable) {
  total += row[ColumnOfInterest];
}

// You do this:
List.Sum(SomeTable[ColumnOfInterest])

Notice how the non-M example involved changing a variable. It’s a good thing Power Query doesn’t require us to write code like this, otherwise we’d be in trouble. Writing iterative code (like the above) often require modifying variables—but with M, this isn’t allowed, for simple variables are immutable!

for, while, do while

In a number of other programming languages, for is use to iterate a certain number of times (e.g. start at 0; add one each iteration; stop when 10 is reached) while while and do while keep looping until a test condition evaluates to false.

If you need something similar in the M world, you could try creating a series of values using one of the list generation functions (like List.Numbers or List.Generate) then apply the appropriate “do to each item” processing to the resulting sequence of values. To implement break-like behavior, use an appropriate library function (like List.Select) to stop processing when you encounter the desired state.

For example, suppose you’re waiting for a long-running remote job to complete. You have a function which checks the job’s status by calling a web service, returning null if the job is still running and returning the job’s results if it has completed. You want to repetitively query the source until either you receive a non-null response (indicating completion) or you’ve tried a certain number of times (a safety cutoff).

In an imperative programming language, you might use a for loop (or maybe a do while loop) with a break clause. In M, you can pull this off using something like:

(MaxAttempts, DelayBetweenAttempts) =>
let
  Numbers = List.Numbers(1, MaxAttempts),
  WebServiceCalls = List.Transform(Numbers, each Function.InvokeAfter(CallWebService, if _ > 1 then DelayBetweenAttempts else #duration(0,0,0,0))),
  OnlySuccessful = List.Select(WebServiceCalls, each _ <> null),
  Result = List.First(OnlySuccessful, null)
in
  Result

Here the beauty of streaming shines. When executed, Result‘s List.First asks OnlySuccessful for a single list item. OnlySuccessful‘s List.Select pulls one value at a time from WebServiceCalls until it finds one that isn’t null, which it returns to Result‘s List.First.

Each time a value is pulled from WebServiceCalls, List.Transform invokes the CallWebService function, which calls the web service. The maximum number of times List.Transform will do this is constrained by the quantity of values in Numbers.

Once the first non-null value has been returned, streaming stops and so calls to the web service also stop. This is true even if unprocessed values remain in Numbers. Since streaming is complete, nothing causes these extra numbers to be streamed through the transform and so trigger web service calls. The net effect is that the web service is queried until it returns a non-null result, up to MaxAttempts number of times. Beautiful!

If your need is to loop indefinitely until the desired state is reached (vs. up to a fixed number of times, like the above example), you can drive that iteration using an infinite series produced by List.Generate:

List.Generate(() => null, each true, each null)

Just keep in mind if you need to loop many, many times, chances are there’s either a non-iterative approach you could (should) be using or you’re doing something outside of Power Query’s forte (beyond mashing up data). In the later case you likely will be better off using a more appropriate language/technology.

(Bonus, before we leave the topic of looping: For an alternate implementation of wait-retry logic that wires in the take action [e.g. call web service] and check for success steps using arguments to List.Generate, see Value.WaitFor‘s description and definition.)

Conclusion

There you have it—you now know about Power Query’s sole control structure: the simple, conditional if. We also examined several M options for situations where you might want to loop or iterate.

By no means are the proceeding examples an exhaustive set of recipes defining how to convert any loop-like statement to the functional Power Query world. Hopefully, though, they will serve as a starting point to help you figure out the M way when you encounter a situation where you feel the need to loop or iterate.

Next Time

What happens if something goes wrong—servers go down, data doesn’t parse, bad things happen? Next time, we’ll explore Power Query’s error handling.

Until then, happy coding!

Revision History

2020-07-31: Added a description of the new null coalescing operator (??).