Replicate Power Pivot’s SWITCH function

The Scenario

Power Pivot has a function called SWITCH(), VBA uses SELECT CASE, and Excel can leverage VLOOKUP to replicate the same functionality.  Regardless of what you call it, we use each of these functions to perform multi-condition logic by declaring a table of index values and results, then passing in a variable index value.  The function then looks up the provided index value (whether passed from an Excel/Power Pivot formula, or a VBA routine) and returns the matching result.  Easier to maintain than several levels of nested IF statements, it sometimes makes sense to replicate Power Pivot’s SWITCH function in Power Query.

The syntax for this function in each of the various technologies looks something like this:

  • Excel:  =VLOOKUP(lookup_value,table_array,col_index_number,[range_lookup])
  • PowerPivot:  =SWITCH(expression,value_1,result_1,[value_2,result_2],…,[Else])
  • VBA:  SELECT CASE:  CASE IS = 1: “Result 1”: CASE ELSE: END SELECT

Download the Workbook

Our Goal

One example of where this can be really useful is when breaking down encoded patterns like customer billing codes, where each character represents something specific.  Take a code like the following: “MP010450SP”, where the 9th character could be one of the following:

E = Employee, S = Yacht Club, N = Non-Taxable, R = Restricted,
I = Inactive, L = Social, M = Medical, U = Regular

To break this apart in Excel, we could build a function with many nested IF statements and build a VLOOKUP based on the letter options. In Power Pivot though, it’s much easier with the SWITCH function as follows:

=SWITCH([Column],
"E","Employee",
"S","Yacht Club",
"N","Non-Taxable",
"R","Restricted",
"I","Inactive",
"L","Social",
"M","Medical",
"U","Regular",
"Undefined")

Please note that there are SEVERAL ways to accomplish this goal.  The question this pattern answers is how to create an equivalent function in Power Query that you can call from other queries.

Step 1: Replicate Power Pivot’s SWITCH function

Building the function isn’t overly difficult once you know the basic structure.  To start with, we need to:

  • Create a new blank query (Power Query  –> From Other Sources  –> Blank Query)
  • Give the query a name like fnSWITCH
  • Go to Home –> Advanced Editor
  • Paste in the M code shown below

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

  • 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 SWITCH statement)

Using this structure, we can modify the fnSWITCH function for our scenario as follows:

(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 the only difference between the two is that we added in all of the available options.  You’re not restricted to looking up values, just make sure that your options are always entered in pairs between curly braces and have a comma at the end of the line.

Once you’re done making the modifications:

  • Click Done
  • Go to Home –> Close & Load

Step 2:  Implementing the fnSWITCH function

Okay, so now we have this, how do we use it?  You’re going to be amazed how easy this is…

First we click inside the data table and create a new Power Query  –> From Table, which results in the following:

pqChoose_1

Next we need to extract the 9th character from the BillingCode column:

  • Go to Add Column –> Add Custom Column
    • Column Name:  Status
    • Formula:  =Text.Range([BillingCode],8,1)

And that gives us the following result:

pqChoose_2

Now that we’ve extracted the character we want, let’s use our function.

  • Click the gear next to the Status step
  • Wrap the existing formula with our function call.
    • =fnSWITCH(Text.Range([BillingCode],8,1))

This gives us the following:

pqChoose_3

End Results

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”.  In the case of the original source of the data, this was entirely expected as valid codes are made up of upper case letters.

If we wanted to accept either case, we would need to modify the Text.Range function to force it to upper case.  This would result in a function call that reads as follows:

=fnSWITCH(Text.Upper(Text.Range([BillingCode],8,1)))

Which would work, as you can see here:

pqChoose_4

Power Query Patterns
Power Query Book
Power Query Workshop

this work was shared 0 times
 400
About

 Power Query Training

  (13 articles)

We are a team committed to delivering the best content for new and seasoned users of a tool that we believe in: Power Query. We firmly believe that this is THE best tool to be added to Microsoft Excel in a long time.

  • Ted Eichinger

    Would be nice to have a downloadable Excel file with this example on it. I’d like to see it, play with it, and be able deconstruct it.

  • Ted Eichinger

    Answered my own question, example Excel file is at the top of the page, downloaded it and got a better understanding, Thanks!

  • Wojciech

    What if I want to return a value from a column in statement and still use this multi conditional logic ? so te return value in one case for example for “U” would be [Boat Type] value in this row ?

    • One of the easiest ways to do this is by changing the SWITCH function code. All you’ll have to do is simply change the “Regular” to be [Boat Type] and that would return the value from the Boat Type column if U is the input value.

      Hope this helps!

  • Scott

    Great post. Thank you for sharing. I tried following the instructions below on returning the value from the Boat Type column but kept receiving an error when U was the input value. Is there a misspelling or do I need to include it in quotes?
    {“M”, “Medical”},
    {“U”, [Boat Type]},
    {input, “Undefined”}
    },

    • Ken Puls, FCPA, FCMA

      Actually, I think you’d have to do a couple of things here. Just inserting [Boat Type] into the function won’t do anything, as it doesn’t know what [Boat Type] is (the function never sees the original table.) To fix this, we’d need to modify the function to also accept the boat type value, and only apply in in the case of that letter being a U. So here’s the function needed:

      (input,boattype) =>
      let
      values = {
      {“E”, “Employee”},
      {“S”, “SCYC”},
      {“N”, “Non-Taxable”},
      {“R”, “Restricted”},
      {“I”, “Inactive”},
      {“L”, “Social”},
      {“M”, “Medical”},
      {“U”, boattype},
      {input, “Undefined”}
      },
      Result = List.First(List.Select(values, each _{0}=input)){1}
      in
      Result

      And then you’d also need to modify the original call to read as follows:
      =fnSWITCH(Text.Range([BillingCode],8,1),[Boat Type])

      Hope that helps!

x