Multi Condition Logic In Power Query

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:

  1. 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.)
  2. 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:

  1. 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.
  2. 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:

SNAGHTML76c03ee

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:

image

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):

SNAGHTML29d7316

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:

SNAGHTML2a06cbf

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:

SNAGHTML2be7f6a

28 thoughts on “Multi Condition Logic In Power Query

  1. 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?

  2. Hi Amanda,

    You could try creating functions in Power Query (see https://www.excelguru.ca/blog/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://www.excelguru.ca/blog/wp-admin/post.php?post=940). You could then remove the code column when you're done.

  3. 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"

  4. 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]

  5. Thanks for the tip.
    Small typo in the code above. One too many blahs in the list...
    {"M", "Medical"},blah
    {"U", "Regular"},

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

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

  8. Hey Chris

    Make sure the datatype is set to whole number/decimal number. Then you wont get the error.

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

  10. 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]

  11. 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?

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

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

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

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

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

  17. 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!

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

Your email address will not be published. Required fields are marked *