SWITCH Function (Multiple If Statements) in Power Query

The typical control structure used in Power Query is the if-statement. It allows you to perform an action based on a condition. In more complex situation, you may add multiple branches in your if-statement. And this can make your code look cluttered and complex. In applications like Excel or a language like DAX, we therefore find the SWITCH function. This function allows you to pass multiple conditions in an easy way. So how can you do such in Power Query? That’s what this article will tell you.

Introduction

In Excel, you can use the SWITCH function in two ways.

1. Mapping input values: the switch function allows you to take an input value and map it to a desired output value.
2. Providing conditions: instead of multiple if-statements, you can provide multiple conditions to the SWITCH function.

The SWITCH structure has become popular in other tools to reduce the amount of code needed, and to easily visualize the conditions. Nesting layers of if-statements can get confusing. The below image shows what that looks like in Excel:

Here, the SWITCH function receives pairs of input and output values, and a default fallback value as its last argument. This is a very effective method to map values from input to a different output. So how can you do these in Power Query?

To start right off the bat, there is no built-in function to perform a SWITCH statement in the M language. You read that right. Yet, Power Query is a functional language that makes good use of objects like lists, records and tables. Knowing this, we can easily replicate some of the SWITCH functionality, by using a different approach.

Using SWITCH to Map Input Values

The first use-case for switch is to map a value to a certain outcome. If you prefer seeing the explanation, you can watch below video:

Let’s look at how this work. Suppose you have a list of country abbreviations of which you want to see the full name. A first approach to try this in M could be:

if [Abbreviation] = "UK"  then "United Kingdom"
  else if [Abbreviation] = "NL"  then "Netherlands"
  else if [Abbreviation] = "FRA" then "France"
  else if [Abbreviation] = "BE"  then "Belgium"
  else if [Abbreviation] = "ITA" then "Italy"
  else "Other"

However, this approach is verbose and repeats the same words again and again.

As an alternative, you could also store these values within a record. We could then use the Record.FieldOrDefault function to perform a lookup and return a default value if there is no match. That could look as follows:

Record.FieldOrDefault( 
  [                              // Record with mapping of 
        UK  = "United Kingdom",  // country abbreviations to full names
        NL  = "Netherlands",
        FRA = "France",
        BE  = "Belgium",
        ITA = "Italy"
  ], 
  [Abbreviation],               // Lookup value in the record      
  "Other"                       // Default return value if no match is found
)

In this code, the Record.FieldOrDefault function uses the value in the [Abbreviation] column and searches for a match within the record in the first argument. If it finds a match, it returns the corresponding full country description. In case there is no match, the function returns the default fallback value, which in this case is "Other".

After adding this code to a custom column, the result is:

This setup mimicks the setup of a SWITCH statement. And especially on longer mapping tables, the reduction in code can make an impact.

So is this a perfect approach that can replicate nested if-statements? Unfortunately not. The method has two downsides:

• Requires Text Values: when using Record.FieldOrDefault, the field names in the record should always be of type text. Therefore, when numbers come in, but the field and the lookup value needs to be transformed to text.
• No Query Folding: at the time of writing, this approach does not fold. Nested if statements would therefore perform better.

To try the example for yourself, you can copy the following code into the advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvVWitWJVvLzAVNuQY5g2skVTHmGALmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Abbreviation = _t]),
    AddCountry = Table.AddColumn(Source, "Country Description", each Record.FieldOrDefault( 
    [ UK = "United Kingdom", NL = "Netherlands", FRA = "France", BE = "Belgium", ITA = "Italy" ],
    [Abbreviation],
    "Other" ) )
in
    AddCountry

Using SWITCH true to provide conditions:

The second way to use SWITCH is to provide conditions to it with corresponding values to return. When the function runs into the first condition that evaluates to true, the corresponding value is returned. In case condition returns true, a default fallback value can be used.

With the SWITCH functionality missing, performing this kind of logic in Power Query is hard. But there have been some people who tried to make this work. The approach I’m showing below is inspired by logic laid out by Daniil Maslyuk: SWITCH and SWITCH( TRUE… in Power Query. You can follow along with the video here:

Using a List for Storing Values and Conditions

Let’s continue our example, by running some conditions on a country abbreviation (UK in this case). We want to label the different countries with a description of what they’re like. UK and NL are both Rainy, in FRA and ITA there’s plenty of opportunity to Wine and Dine and in Belgium (the remaining) you can enjoy your Fries.

To provide both conditions and corresponding return values (to mimic the SWITCH function), we can make use of a list. Here’s an example:

// Output: { true, "Rainy", false, "Wine and Dine", "Fries" }
let
  Abbreviation = "UK",
  myList = 
    { 
      List.Contains( {"UK" , "NL" }, Abbreviation ), "Rainy", 
      List.Contains( {"FRA", "ITA"}, Abbreviation ), "Wine and Dine", 
      "Fries" 
    }
in
  myList

In this example:

• the conditions are provided as first and third items in the list.
• The corresponding return values are the second and fourth list items.
• In case none of the conditions match, the default value in the fifth list item should be returned.

Splitting up the List

To ensure the function knows what to return, it would be easiest if we can create a list of conditions and a list of results. We can do that by using the List.Alternate function as follows:

let
  Abbreviation = "UK",
  myList = 
    { 
      List.Contains( {"UK" , "NL" }, Abbreviation ), "Rainy", 
      List.Contains( {"FRA", "ITA"}, Abbreviation ), "Wine and Dine", 
      "Fries" 
    },
  Conditions = List.Alternate( myList, 1, 1, 1), // output: { true, false, "Fries" }
  Results = List.Alternate( myList, 1, 1, 0 )    // Output: { "Rainy", "Wine and Dine" }
in Results 

Retrieving the Relevant Result

Great. Now let’s forget about the default fallback value for now. We first need to figure out how to return the relevant results for the SWITCH Function. When one of the ‘conditions’ returns true, we can return the corresponding value. To do that, we need to find the position of the first condition that returns true. Based on its position, we can then return the corresponding item from the results query.

This is what we could try:

// Returns "Rainy" for the "UK" abbreviation
let
  Abbreviation = "UK",
  myList = 
    { 
      List.Contains( {"UK" , "NL" }, Abbreviation ), "Rainy", 
      List.Contains( {"FRA", "ITA"}, Abbreviation ), "Wine and Dine", 
      "Fries" 
    },
  Conditions = List.Alternate( myList, 1, 1, 1), // output: { true, false, "Fries" }
  Results = List.Alternate( myList, 1, 1, 0 ),   // output: { "Rainy", "Wine and Dine" }
  indexToReturn = List.PositionOf( Conditions, true ), // find position of 1st true value
  outcome = Results{indexToReturn} // return corresponding item from the results list
in 
  outcome 

In this code, the List.PositionOf function searches for the first true value, and returns its position. The outcome step then returns the relevant item from the results list.

Including a Default Fallback Value

So far, the code has given us conditions and results, but we ignored the default fallback value. In this case, the value “Fries” is contained within the Conditions list. And in some others scenarios, you might not always have a fallback condition.

Therefore it’s good to check whether the number of items provided to the original list is uneven. If that’s the case, there is a default value available and we can return it when no conditions return true. We can test that by writing:

// Returns "Fries" for this example. In case there is no fallback value, it returns null
  Default = if Number.IsOdd( List.Count( myList) ) then List.Last( myList ) else null 

The code tests whether the number of items in the list (returned by List.Count) is odd by using the Number.IsOdd function. If that’s the case, it returns the last item from the list by using List.Last, in other cases it returns null.

Great, now we need to figure out how to return the relevant results for the SWITCH Function. We already have the logic in place to return the corresponding values of conditions that evaluate to true. However, when no conditions return true, the List.PositionOf function will return -1 as a result. And we still want to be able to return the fallback value.

One way to solve this, is to increment the result of the indexToReturn step by 1. In case no condition returns true, the List.PositionOf function then returns -1 + 1 = 0. If we then prepend the Fallback value to the start of the Results list, it will always be returned when conditions don’t evaluate to true.

Incorporating that logic would look like:

let
  Abbreviation = "UK",
  myList = 
    { 
      List.Contains( {"UK" , "NL" }, Abbreviation ), "Rainy", 
      List.Contains( {"FRA", "ITA"}, Abbreviation ), "Wine and Dine", 
      "Fries" 
    },
  Conditions = List.Alternate( myList, 1, 1, 1), // output: { true, false, "Fries" }
  Results = List.Alternate( myList, 1, 1, 0 ),   // output: { "Rainy", "Wine and Dine" }
  Default = if Number.IsOdd( List.Count( myList) ) then List.Last( myList ) else null,
  // combine default value with the regular results list
  CombinedResults = {Default} & Results,
  // searches first condition to return true. Returns 0 otherwise
  indexToReturn = List.PositionOf( Conditions, true ) + 1,
  // extract the relevant value from the CombinedResults list
  outcome = CombinedResults{indexToReturn}
in 
  outcome 

Turn logic into a function

We can turn this logic into a function. The only thing the user needs to provide is a list with both the conditions and the corresponding return values. We can do that as follows:

( conditionsList as list ) as any =>
let
  myList = conditionsList ,
  Conditions = List.Alternate( myList, 1, 1, 1), // output: { true, false, "Fries" }
  Results = List.Alternate( myList, 1, 1, 0 ),   // output: { "Rainy", "Wine and Dine" }
  Default = if Number.IsOdd( List.Count( myList) ) then List.Last( myList ) else null,
  // combine default value with the regular results list
  CombinedResults = {Default} & Results,
  // searches first condition to return true. Returns 0 otherwise
  indexToReturn = List.PositionOf( Conditions, true ) + 1,
  // extract the relevant value from the CombinedResults list
  outcome = CombinedResults{indexToReturn}
in 
  outcome 

Notice that the code no longer contains the ‘Abbreviation’ step and requests a conditionsList as input. If you save the above function logic in a function (query name) called SWITCH, we can call it like in below image:

To run this for yourself, you can use the below code snippet. Just make sure to add the above function code in a query named SWITCH, or the below code will not work:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvVWitWJVvLzAVNuQY5g2skVTHmGALmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Abbreviation = _t]),
    AddDescription = Table.AddColumn(Source, "Description", each 
SWITCH( { List.Contains( {"UK" , "NL" }, [Abbreviation] ), "Rainy", 
          List.Contains( {"FRA", "ITA"}, [Abbreviation] ), "Wine and Dine", 
          "Fries" } ) )
in
    AddDescription

So let’s reflect on what we did here. We created a list of values to retrieve that include both conditions and return values. Since the SWITCH function requires condition and return value to come after each other, we used some list functions to reshape our lists. We then added logic to lookup the relevant return values when a condition evaluates to true, or to return a default value when no condition is true.

Just like with the earlier method shown, this SWITCH variant does not fold. For performance reasons, I therefore recommend sticking with the the if-then-else control structure available in the M language. However, this was a fun thought experiment to run through.

Conclusion

In this article, you learned that with a bit of creativity, you can remap your values using a record structure, or build your own SWITCH statement. Unfortunately, the performance of these is not any better than using the regular control structures.

So until the Power Query team releases an actual SWITCH function, these workarounds belong to the category ‘fun to use’ rather than ‘effective techniques’. I still hope you enjoyed the read. Enjoy Power Query.