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
 800
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!

  • Steve Jakubowski

    Use record instead of list for the pairs along with Record.FieldOrDefault().
    For field names that are not valid M names, use #”…”, for example: [#”33″ = “Rolling Rock”]

    • Hey Steve!

      Thanks for the comment and the suggestion!

      The thing with record fields is that their names need to be text, which might not work for every case, especially when you’re trying to extend this pattern into something else using numbers, dates or even binaries. – yes, binaries! (crazy thing, but I had to do it at one point).

      Here’s another example of customizing this pattern to be create a function that needs to use Numbers instead of text:

      PopulationCategory = (PopulationPtr as number) =>
      let
      Source = {
      {1000000,” 1B”}
      },
      Custom1 = List.Select(Source,
      each _{0} > PopulationPtr),
      Source1 = Custom1{0},
      Source2 = Source1{1}
      in
      Source2;

      and then here’s me using the function inside a query that you can also check out:

      WorldPopulation = () as table =>
      let
      Source = Csv.Document(Web.Contents(“https://www.cia.gov/library/publications/the-world-factbook/rankorder/rawdata_2119.txt”),null,{0, 7, 58},null,1252),
      #”Changed Type” = Table.TransformColumnTypes(Source,{{“Column1”, Int64.Type}, {“Column2”, type text}, {“Column3″, type number}}),
      #”Invoked Custom Function” = Table.AddColumn(#”Changed Type”, “PopulationCategory”, each PopulationCategory([Column3])),
      #”Changed Type1″ = Table.TransformColumnTypes(#”Invoked Custom Function”,{{“PopulationCategory”, type text}}),
      #”Grouped Rows” = Table.Group(#”Changed Type1″, {“PopulationCategory”}, {{“Total Countries”, each Table.RowCount(_), type number}, {“Total Population”, each List.Sum([Column3]), type number}}),
      #”Added Custom” = Table.AddColumn(#”Grouped Rows”, “Custom”, each List.Sum(#”Grouped Rows”[Total Population])),
      #”Inserted Division” = Table.AddColumn(#”Added Custom”, “Inserted Division”, each [Total Population] / [Custom], type number),
      #”Removed Columns” = Table.RemoveColumns(#”Inserted Division”,{“Custom”}),
      #”Changed Type2″ = Table.TransformColumnTypes(#”Removed Columns”,{{“Inserted Division”, Percentage.Type}}),
      #”Added Custom1″ = Table.AddColumn(#”Changed Type2″, “Custom”, each List.Sum(#”Removed Columns”[Total Countries])),
      #”Inserted Division1″ = Table.AddColumn(#”Added Custom1″, “Inserted Division.1″, each [Total Countries] / [Custom], type number),
      #”Removed Columns1″ = Table.RemoveColumns(#”Inserted Division1”,{“Custom”}),
      #”Changed Type3″ = Table.TransformColumnTypes(#”Removed Columns1″,{{“Inserted Division.1″, Percentage.Type}}),
      #”Renamed Columns” = Table.RenameColumns(#”Changed Type3″,{{“Inserted Division”, “% Population”}, {“Inserted Division.1”, “% Countries”}})
      in
      #”Renamed Columns”;

      • Steve Jakubowski

        I’m surprised to find a reply to my terse comment on an older post. Thanks!

        You make a good point that field names in a record must be text (a.k.a. “string”), but for many lookup situations that’s sufficient, and it’s what this SWITCH tutorial does too.

        Your example is cool because it takes things to the next level where the comparison isn’t just a straight lookup but the lookup key exists within a range of values, and it’s a number to boot, not just text, so a record field just won’t cut it for that.

        But whenever the lookup is a straight-up text match (often, I’d say) I would still choose record over list. It’s simpler to implement in that case.

        • Steve Jakubowski

          (input) =>
          let
          no_match = null,
          values = [ E = “Employee”, S = “SCYC”, N = “Non-Taxable” /*, etc */ ],
          Result = Record.FieldOrDefault(values/* oops */, input, no_match)
          in
          Result

          • Steve Jakubowski

            /* better, probably */
            let
            no_match = null,
            values = [ E = “Employee”, S = “SCYC”, N = “Non-Taxable” /*, etc */ ],
            lookup_fn = (input) => Record.FieldOrDefault(values, input, no_match)
            in
            lookup_fn

          • Don’t get me wrong, the record approach is valid, I just wouldn’t categorize it as an approach comparable to a “SWITCH” function because it has some limitations that SWITCH doesn’t have and you also need to do some input translations for inputs that have spaces and need to be translated to the #”Name” form

            We felt that the exact lookup was the ‘cleanest’ way to showcase this function, but this pattern has many applications that would be too long to write in a single post. The truth is that we wanted to portray the same functionality that the SWITCH function in DAX offers where you can do either exact matches or go with other types of look ups like the one described in the previous comment.

            We might need to create a part 2 to showcase this! I’ll talk with Ken about this 🙂

      • Steve Jakubowski

        /* for reference */
        (input) =>
        let
        no_match = null,
        values = [ E = “Employee”, S = “SCYC”, N = “Non-Taxable” /*, etc */ ],
        Result = Record.FieldOrDefault(input, no_match)
        in
        Result

x