In my last post, we looked at creating an IF statement using Power Query. This time we’re going to go a bit deeper and look at multi condition logic in Power Query, exploring a scenario where we need to choose between several options.
The Base Scenario
In the last post I talked about my billing code setup. As a reminder, it’s a 10 digit code that is constructed as follows:
- Char 1 – Alpha – Indicates the division (G = Golf, F = Fitness, M = Marina)
- Char 2 – Alpha – Indicates the billing type (D = Dues, S = Pass, A = Annual Moorage, P = Periodic Moorage)
- Char 3-4 – Numeric – Indicates the number of months of coverage for the product (1-12)
- Char 5-6 – Numeric - Indicates the start month (and subsequent anniversary) for the customer’s product
- Char 7-8 – Variable – Slip length (in feet) for a boat in the case of marina customers, or SG, CP or CS for golf (indicating single, couple primary or couple spouse)
- Char 9 – Text – A variety of single letter codes indicating specific things we want to know. (Outlined below)
- Char 10 – Text – Indicates the payment method (F = Financed, P = Paid up front, C = Comp/Honorary)
(Note that the sample data only includes records for Marina data)
Sample File
If you’d like to follow along, download the sample file here.
Multi Condition Logic – Using IF
So, building on my previous two posts (using text functions and creating IF statements), we could easily break the first character into pieces by nesting 2 IF tests together:
=if Text.Start([BillingCode],1)="G" then "Golf" else "two options left"
=if Text.Start([BillingCode],1)="F" then "Fitness" else "Marina"
into
if Text.Start([BillingCode],1)="G" then "Golf" else if Text.Start([BillingCode],1)="F" then "Fitness" else "Marina"
Not too hard really. In fact, we can even build each IF statement separately, then just copy the second to replace the “two options left” part without making any other changes at all. No parentheses or anything needed.
More Than 3 Options
But what if you have a whole bunch of options that you need to work with? Let’s look at the 9th character in our billing code. I haven’t given the details yet for that one, but here are the options:
E = Employee, S = Yacht Club, N = Non-Taxable, R = Restricted, I = Inactive, L = Social, M = Medical, U = Regular
Wow. That’s a whole lot of possibilities, and would make for one monster nested IF statement. That wouldn’t be a lot of fun to write, nor maintain. So how would we deal with it?
In Excel proper, we would probably separate these options into a separate table, then use VLOOKUP to pull the appropriate value into the table. So we just need a Power Query VLOOKUP function… except there isn’t one.
We actually have a couple of different methods to deal with this. We could either:
- Split the first character into one column, create an Excel table with the first letter in column 1 and the appropriate match in column 2, then merge the two using Power Query’s merge function. (Maybe I’ll write a future post on it.)
- Build our own CHOOSE function inside Power Query (or SWITCH if you prefer Power Pivot’s DAX version.) This is way more fun, so let’s do that. 🙂
Building a CHOOSE Function
This actually isn’t too hard once you know the basic structure. It basically goes like this:
function_name = (input) => let
values = {
{result_1, return_value_1},
{input, “Undefined”}
},
Result = List.First(List.Select(values, each _{0}=input)){1}
in
Result,
The key parts to recognize here are:
- We can change the “function_name” part to be whatever we want/need,
- result_1 is the first of the possibilities that we may pass TO the function
- return_value_1 is the value that we’d like to return if the first value is result_1
- if we need more values, we just insert another comma after the value_1 section and put in a value_2 section
- we can keep adding as many values as we need.
- the “Undefined” value will return the text “Undefined” if the value you pass isn’t in your provided list of options (it’s the Else portion of the CHOOSE statement)
Using this structure, we could write a CHOOSE function for our scenario as follows:
fnChoose_CustCode = (input) => let
values = {
{"E", "Employee"},
{"S", "SCYC"},
{"N", "Non-Taxable"},
{"R", "Restricted"},
{"I", "Inactive"},
{"L", "Social"},
{"M", "Medical"},
{"U", "Regular"},
{input, "Undefined"}
},
Result = List.First(List.Select(values, each _{0}=input)){1}
in
Result,
Notice that I changed a couple of things:
- I gave the function a name so that I can recognize it, and also so that I can create more than one function with different names. This one is fnChoose_CustCode.
- I created a list of all the options I needed.
Implementing the CHOOSE Function
Okay, so now we have this, how do we use it? Again, we’ve got two options. I’ll focus on the other option at some other time, but for this scenario I want to build this right into an existing query. So here’s how I do it.
First I created a brand new query that just pulls my table into Power Query, resulting in the following:
Let’s go and inspect the code that pulls this in. We need to click View –> Advanced Editor. That will bring up the following code:
let
Source = Excel.CurrentWorkbook(){[Name="Customers"]}[Content]
in
Source
Not too complicated (yet). Let’s paste in our code just before the Source = line:
let
fnChoose_CustCode = (input) => let
values = {
{"E", "Employee"},
{"S", "SCYC"},
{"N", "Non-Taxable"},
{"R", "Restricted"},
{"I", "Inactive"},
{"L", "Social"},
{"M", "Medical"},blah
{"U", "Regular"},
{input, "Undefined"}
},
Result = List.First(List.Select(values, each _{0}=input)){1}
in
Result,Source = Excel.CurrentWorkbook(){[Name="Customers"]}[Content]
in
Source
Perfect. And yet it doesn’t exactly look like much. In fact, beyond adding a new line in the Steps section of the Editor, we don’t see any changes:
So what good did that do then?
As it turns out, we’ve only pasted in our function to make it available to the Power Query engine. We haven’t actually told Power Query to do anything with it. So why don’t we do that now?
Using Our New CHOOSE Function
You’re going to be amazed how easy this is…
First we’re going to add a new column (Add Column –> Add Custom Column). When the dialog pops up, we’ll create a formula to return the letter we want to pass to the function:
=Text.Range([BillingCode],8,1)
And that gives us the following result (assuming we provided the column name of Status):
Cool stuff. Now, let’s amp this up and use our function. We’ll click the gear next to the Status step and wrap the existing formula with our function call. (Don’t forget the extra parenthesis needed at the end):
=fnChoose_CustCode(Text.Range([BillingCode],8,1))
Which gives us the following:
The End Effect
You’ll find that all the sample codes in the data work just fine, and that nothing comes back as undefined. If you’d like to see how the query reacts to different items, go back to the Customers table and try changing the second to last letter to something else. When you refresh the table, you’ll find that it will evaluate the new character and return the appropriate result.
Caveat
It should be noted that the function as written above is case sensitive, meaning that a code of MP010450uP would return “Undefined”. This is expected in my case, as valid codes are made up of upper case letters.
If I wanted to accept either case I would need to modify my Text.Range function to force it to upper case. This would result in a function call that reads as follows:
=fnChoose_CustCode(Text.Upper(Text.Range([BillingCode],8,1)))
Which would work, as you can see here:
35 thoughts on “Multi Condition Logic in Power Query”
Hi, Thanks for the above. I've created a very long "If Statement" in Power Query, but want to know if there is a smarter way to do this. An example of what I would like to do:
If the product is a shirt and the colour is red and the size is medium, then calculate 15% of the cost price. I've got several scenario's like this that I need to combine into one formula. Some scenario's have 2 conditions, others 3 and so on, but for each list of conditions, a specific calculation has to be done. Advice please?
Hi Amanda,
You could try creating functions in Power Query (see https://excelguru.ca/wp-admin/post.php?post=817). I haven't yet tried calling a function from within another function though... will have to play around with that.
An easier method might be to combine a few characters from Product, Colour and Size into a code, build a lookup table of codes separately, then merge them together using Power Query's merge function (shown in use in this post https://excelguru.ca/wp-admin/post.php?post=940). You could then remove the code column when you're done.
Thanks so much for showing this. EXACTLY what I needed!
Hm, I see this is similar to the approach Chris Webb took (https://cwebbbi.wordpress.com/2014/03/10/conditional-logic-in-power-query/), though adapted to a simpler situation here (exact match rather than binning). I was just facing the same issue, then... just realized that the easiest solution is actually just Record.Field([E="Employee", S="SCYC", ...], input). 🙂
Tycho, this is pretty cool. One question though... what about the "else" condition? Is there a way to find that, or do we need to wrap it in a try/otherwise as follows:
try Record.Field([E="Employee", S="SCYC", N="Non-Taxable", R="Restricted", I="Inactive",L="Social",M="Medical",U="Regular"], Text.Range([BillingCode],8,1)) otherwise "Undefined"
Hey,
I suppose you could check it like:
if Record.HasField(rec, input) then Record.Field(rec, input) else "nope!"
... or as you did, relying on Record.Field to error when the input isn't in there.
There might be performance differences, though if so I'm not sure which version wins. If you're interested though, this might help for profiling (untested):
[code language="cpp"]
let Profile = (fn as function, params as list) =>
let
TimeBefore = DateTime.LocalNow(),
evaluated = Function.Invoke(fn, params),
TimeAfter = (try evaluated as none otherwise DateTime.LocalNow()),
TimeTaken = TimeAfter - TimeBefore
in
TimeTaken
in Profile
[/code]
Awesome, thanks!
Thanks for the tip.
Small typo in the code above. One too many blahs in the list...
{"M", "Medical"},blah
{"U", "Regular"},
Fixed, thanks!
You could also use List.Contains
let
Source = Excel.CurrentWorkbook(){[Name="Customers"]}[Content],
_expanded.status = Table.AddColumn(
Source,
"Status",
each if List.Contains(
{"E","S","N", "Non-Taxable","R","I","L","M","U"},
Text.Range([BillingCode],8,1))
then Record.Field(
[E="Employee", S="SCYC", N="Non-Taxable", R="Restricted", I="Inactive",L="Social",M="Medical",U="Regular"],
Text.Range([BillingCode],8,1))
else "Undefined"
)
in
_expanded.status
Awesome, thanks Bob!
Ken,
How do I create if statements with the greater than and less than feature. I tried to use the standard excel but i just received errors.
Hi Chris,
It should be as follows:
= if test > x then do_if_true else do_if_false
Hey Chris
Make sure the datatype is set to whole number/decimal number. Then you wont get the error.
Can you help me with a formula problem in power query
This is what it would look like in excel
If({inv adj code 1]=””,[Line amount],if([Inv adj code]=”A”, [Line amount]+[Inv adj amt 1], [Line amount]-[Inv adj amt 1]))
After some google time (with you)this is what I came up with for Power Query. It works for everything but if [Inv adj code 1]="A" it is doing [Line amount]-[Inv adj amt 1] instead of [Line amount]+[Inv adj amt 1]
if [Inv adj code 1]="" then if [Inv adj code 1]="A" then [Line amount]+[Inv adj amt 1] else [Line amount] else [Line amount]-[Inv adj amt 1]
Any assistance you could provide would be much appreciated
Hi Ruth,
I think you're looking for this:
if [Inv adj code 1]=""
then
(
if [Inv adj code 1]="A"
then [Line amount]+[Inv adj amt 1]
else [Line amount]-[Inv adj amt 1]
)
else [Line amount]
How would you use switch with a whole number argument, such as putting a dollar amount into an aging bucket based on days from invoice to a month end?
Hi Lewis,
Have a look at this article: https://excelguru.ca/2016/02/29/creating-a-banding-function-in-power-query/
Ken,
I went to your post on the banding function. It was exactly what I needed. I already had a field that calculated a difference between two dates, so implementing the banding function was easy. Thanks for your help.
Hey Ken,
I had a folder with excel workbooks. I have a key word that i filter the workbooks by in power query. However, when I add a new workbook to folder I have to go back into the query and re-type my key word and select all. Do you know of a way to have power query do this for me? aka filter by key word and always select all?
Thanks
Solved my own simple question...Text filters that contains does the job...apologies
Perfect! 🙂
How can I use this as an exception/override function, while defaulting back to the existing value in the input column if it's not an exception value? I can't seem to get this to work:
= (input) => let
values = {
{"E", "Employee"},
{"S", "SCYC"},
{"N", "Non-Taxable"},
{"R", "Restricted"},
{"I", "Inactive"},
{"L", "Social"},
{"M", "Medical"},
{"U", "Regular"},
{input, [Billing Code]}
},
Result = List.First(List.Select(values, each _{0}=input)){1}
in
Result
Okay, you'd have to make a couple of changes. The signature function would need to also include the original field details like this:
(input, code) =>
let
values = {
{"E", "Employee"},
{"S", "SCYC"},
{"N", "Non-Taxable"},
{"R", "Restricted"},
{"I", "Inactive"},
{"L", "Social"},
{"M", "Medical"},
{"U", "Regular"},
{input, code}
},
Result = List.First(List.Select(values, each _{0}=input)){1}
in
Result
And then you'd also need to modify the calling query to also include the column as a second parameter for the SWITCH function.
This is a great site, but do you have a section for 'Dummies'? I was searching for something that would help me isolate data from a cell, if the string had more than 5 characters, and if it was less than 5 characters, report data from a different column. I know how to do that in good-old-excel, but this language has me stuck.
Hey George,
So the good news here is that this post is pretty old, and there has been new functionality added. So here's what you want to do:
-Select the column with the word, got to Add Column --> Extract --> Text Length
-Now go to Add Column --> Conditional column
Use the expression builder to check if length > 5 then Column 1 else Column2
It's 100% UI driven, not code.
Hope this helps!
You all are awesome for helping and sharing your knowledge.
Thank you Alicia, we appreciate you taking the time to read!
Hi Ken! I am using variables, to read numbers ( salaries ) into. That works fine. I could really use your delicious function, to make the next column show the amount from the variables.
I have tried this.. (sorry about the long code..):
LonTrin = Excel.CurrentWorkbook(){[Name="LontrinTabel"]}[Content],
LTm2 = LonTrin{0}[SUF],
LTm1 = LonTrin{1}[SUF],
LT00 = LonTrin{2}[SUF],
LT01 = LonTrin{3}[SUF],
LT02 = LonTrin{4}[SUF],
LT03 = LonTrin{5}[SUF],
LT04 = LonTrin{6}[SUF],
LT05 = LonTrin{7}[SUF],
LT06 = LonTrin{8}[SUF],
LT07 = LonTrin{9}[SUF],
LT08 = LonTrin{10}[SUF],
LT09 = LonTrin{11}[SUF],
LT10 = LonTrin{12}[SUF],
//
fnChooseLT_One = (input, ??) => let
values = {
{-2, LTm1},
{-1, LT00},
{0, LT01},
{1, LT02},
{2, LT03},
{3, LT04},
{4, LT05},
{5, LT06},
{6, LT07},
{7, LT08},
{8, LT09},
{9, LT010},
{10, LT10},
{999, 1},
{input, ??}
},
Result = List.First(List.Select(values, each_{0}=input)){1}
in
Result,
After several lines of boring code, I now have a column, with the name "EksaktLontrin" and would like to make a new column with the function showing the amount, so I make this code:
"=fnChooseLT_One([EksaktLontrin])"
The error text says, that there is a parameter missing, but I have no clue of which and also no clue about the "original field" you mention earlier in this thread (thats why the "??" in the function code.
What am I missing here?
Honestly, this question is probably better asked in the forums at http://www.excelguru.ca/forums. 🙂
Hi Ken,
I'm sure you're aware of this but you can trick PQ into doing multi-statement if-else constructs by using records! For example ...
result =
if flagFalse
then
"try again"
else
[
command1 = something,
command2 = something else,
command9 = if command1 > command2 then "ok" else "doh!"
][command9] // return the final value
,
The above is meaningless but the logic is there.
- Remember to not comma-end the last statement in the record.
- Return the final value from the record as the result.
I just stumbled on the above because I wanted to carry out a few steps in a common function which were not relevant to all records, unfortunately PQ doesn't let you jump to the end of a function or quit a function early, so I tried the above and it worked! The alternative would have been to either keep checking flags OR create another function and have to pass all the data from the first function to the second function, I think this is tidier and more efficient.
I hope this is of interest to you and/or your readers.
Nick
Thanks Nick!
Off-topic here but looking for work and "love" playing with PQ and am (now) a bit of a data wizard but nobody will consider me because I don't have commercial experience. How do you get commercial experience, by having a job ... how do you get a job, by having commercial experience ... catch-22! So, if there's anybody out there who would be willing to give an unemployed youngish middle-aged guy a shot, please contact me.
Sorry Ken.
Pingback: ?Excel??Power Query????CHOOSE????????????? – Adzuki & Genmai
Thank you Ken! This is great!