Experiments with SwitchMethod() in Power Query

“Come with me if you want to Switch()…”

I have posted three times now about ways to think about a custom Switch() function in Power Query. I was thinking though, what if you want to switch based on whether the text contained the key? Or if it started with the key? That seemed doable considering the set up I had.

fnSwitchMethod()

Here is the code I put together (Gist is here):

let fnSwitchMethod = 

 (string as text, switches as list, default as any, optional method as number) =>
 
 let 
    check_method = if method <> null then method else 0, 
    case = if check_method = 0 then 
              try List.Select(switches, each _{0} = string){0}{1} otherwise default
            else if check_method= 1 then 
              try List.Select(switches, each Text.Contains(string,_{0})){0}{1} otherwise default
            else if check_method = 2 then 
              try List.Select(switches, each Text.StartsWith(string,_{0})){0}{1} otherwise default
            else if check_method = 3 then 
              try List.Select(switches, each Text.EndsWith(string, _{0})){0}{1} otherwise default
            else default

    in 
    case,
    
  fnType = type function (string as text, switches as list, default as text, optional method as number) as any
    meta [
      Documentation.Name = "fnSwitchMethod", 
      Documentation.LongDescription
        = "This function takes a string and performs a SWITCH replacement using nested lists and different methods. #(lf)" &
        "Additionally, it provides a default value if there are no matches. #(lf)" &
        "Default method or 0 is match (#lf)" &
        "Method 1 is a switch contains (#lf)" &
         "Method 2 is a switch starts with (#lf)" &
         "Method 3 is a switch ends with (#lf)" 
    ]
in
  Value.ReplaceType(fnSwitchMethod, fnType)

How Does It Work?

The code takes 4 parameters:

• String, which is the value you want to operate on
• Switches, the nested list of key-value pairs that you want to check and replace
• Default, the default value if there is no match
• Method, the switch condition/method you want to use

The optional method parameter defaults to zero, which is a traditional switch function. If you pass in one, then the conditional check on each key will be a “contains” check. What this means is that if the target text contains the search text, then the switch will be made. Passing in a two will do a “starts with” check and passing in a three will do a “ends with” check.

There are probably some things to watch out for. One thing I am thinking of now is if the column has a lot of text in it and there is a match on multiple keys. This could cause unexpected results.

How Do I Use This?

After adding the function, add a custom column and pass in the parameters. Remember to structure the nested list with curly brackets.

How Useful Is This?

Who knows? Thinking about functions in this way is helpful, because you can see the flexibility of M. I also just enjoy functional programming a lot, which is pretty nerdy but I am one so that is ok.