Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

avisingh

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

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 - attached to post.

 

Power On!
-Avi Singh

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

Follow me on LinkedIn, Twitter

Comments

Great post!

Sandy indicated we should have your modeling session down as a Webinar.

As i never heard back from Rob should i scratch his name and update the title?

Thanks AGain

Chuck

 

Great idea to make PQ more flexible. This is exactly what I was looking for!

Anonymous

Thank you for this.
Cheers.

Just like to share my approach on this.

 

How I branched my power query code was using the below format:

 

let

    <your steps>

    finalResult =

        if <condition> then

            let

                <your steps>

                 trueResult = <your last step>

            in

                trueResult

        else

            let

                <your steps>

                falseResult = <your last step>

            in

                falseResult

in

    finalResult

 

Happy to help and contribute to the community