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

Posted on by

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.

Power_Query_Add_Custom_Column
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.

Power On!
–Avi Singh

7 thoughts on “Conditional Code Branching in Power BI Query: if…then…else => then…else…if”

  1. Very nice article. Power Query looks much more powerful than it is thought to be. This makes the data models more efficient along with DAX.

    Thanks for sharing the same.

    -Deepak

  2. Hi Avi,

    Another way of doing this would be something like this (apologies for the messed up formatting):

    let
    Orig_Source = Products_Source,

    result =
    if ManufacturingTeam = “Clothing”
    then
    //Adjustments for Team Clothing: Add 5 to ListPrice, Color: Change “Multi” to “Rainbow”
    let
    #”Clothing_AdjustListPrice” = Table.TransformColumns(Orig_Source, {{“ListPrice”, each List.Sum({_, 5}), type number}}),
    #”Clothing_AdjustProductColor” = Table.ReplaceValue(#”Clothing_AdjustListPrice”,”Multi”,”Rainbow”,Replacer.ReplaceText,{“Color”}),
    #”Clothing_Result” = #”Clothing_AdjustProductColor”
    in
    #”Clothing_Result”

    else

    //Adjustments for Team Components: Filter to Product Name does not contain “4”, Color: Change “Black” to “Black is Back”
    let
    #”Components_FilterOutProduct4″ = Table.SelectRows(Orig_Source, each not Text.Contains([ProductName], “4”)),
    #”Components_AdjustProductColor” = Table.ReplaceValue(#”Components_FilterOutProduct4″,”Black”,”Black is Back”,Replacer.ReplaceText,{“Color”}),
    #”Components_Result” = #”Components_AdjustProductColor”
    in
    #”Components_Result”

    in
    #”result”

  4. Sometimes I’ll use if/then/else to check for an error condition (e.g. null) before using a variable. A custom error message can help when debugging:
    error “null argument arg detected in function xyz”

    To keep the if expression short and readable, define the error in its own step:

    NullArgumentError = error “null argument in query xyz”,
    Result = if argnull then StepThatUsesArg else NullArgumentError

  5. oops. “not equals” angle brackets were stripped by the comment system 🙂

    Result = if arg = null then NullArgumentError else StepThatUsesArg

  6. Is it a way to add the parameter “ManufacturingTeam” as a slicer to be a part of visualization?

Comments are closed.