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

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:

"S","Yacht Club",

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) =>
values = {
{result_1, return_value_1},
{input, "Undefined"}
Result = List.First(List.Select(values, each _{0}=input)){1}

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) =>
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}

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:


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:


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:


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.


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:


Which would work, as you can see here:


Power Query Patterns
Power Query Book

this work was shared 0 times

 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.