Conditional Code Branching in Power BI Query: if...then...else => then...else...if

[avisingh]

If...Then...Else in a Custom Column

We have all used an "if...then...else" statement, when adding a custom column in Power BI query (using Excel Power Query, or Power BI > Get Data). Here is a quick example from our book, Chapter 20 "Power Query to the Rescue", Scenario #3 – Adding Custom Columns to Your Lookup Tables.

Adding a custom column using if…then…else

If...Then...Else for Conditional Code Branching

However in my previous life, I was used to using if…then…else for conditional code branching when using programming languages (VBA counts, right?). Based on the if condition, code execution follows two completely separate paths.

Conditional Code Branching, lets you follow separate execution paths based on condition

This is easily done in most programming languages, but initially I could not quite figure this out for a Power BI query. One thing that kept tripping me up was to think of M as a Functional Language.

M is a Functional Language

As Matt outlined in an earlier post, M is a functional language, with each line of code is in this format:

myResult = myFunction(some parameters),

You can see this clearly in the sample M code below.

Sample M Code, each line is a like a Function call

Now, typically when you build a query, using the buttons available on the ribbon – each step would link with the next step. This is evident if we focus only on the step names in the code shown above.

Typically each step in M, links with the next step

However, this is not a requirement! In fact you have full flexibility – a given step can reference any other step. It takes a bit of shift to get this the first time. But once you do, you can see that an easy approach opens up to achieve conditional code branching.

Then…Else…If…

Here is how I did it – instead of thinking of it as if…then…else, I just switched the order to then…else…if.

Conditional Code Branching: Instead of thinking of it as if…then…else, I just switched the order to then…else…if

Rethink if…then…else as then…else…if in M

Here is what it looks like in some real M code (download PBIX file😞

Code branching in M (Power BI Query)

My understanding is that a given step is only executed if it is needed to return the final result. So in theory, based on the condition only the “THEN” block or the “ELSE” block would be executed. Also, you could place the “THEN” block and the “ELSE” block after the “If” statement, that is a matter of preference.

Real Life Scenario for Code Branching

The scenario where I ended up using this pattern was as below:

• A Power BI Model I had built ended up being used by not one but two different teams
• This was doable, as they were running copies of the same model, just with a different set of Input files.
• The input files for both teams were identical (Sales/Finance data coming from their Enterprise system)

The only catch was…

• Each team had a customized set of tweaks, a very custom set of steps (M transformations) that needed to be ran. For this I ended up using the “then…else…if” blocks as I describe above

I realize there would be many ways to solve this problem. But this approach worked for me, and also helped me get a bit more comfortable with the Functional Language nature of M.

Download PBIX file with the code shown in this post - attached to post.

Power On!
-Avi Singh

Power BI Author, Trainer, Consultant based near Seattle, WA

Follow me on LinkedIn, Twitter

Power_BI_Query_If_then_else.pbix