Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
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.
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.
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.
The scenario where I ended up using this pattern was as below:
The only catch was…
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.