SWITCH() and Power Query – Part Deux

Can Power Query use conditional recursion to replicate a SWITCH() function?

Iterate on the words below to find out.

Yesterday I posted about how you could use Power Query to replicate a SWITCH() function. I initially used List.Accumulate() to do this and it worked. However, it was not the greatest solution, for one main reason.

The way I wrote it was that no matter if a match was found on the first item, List.Accumulate() would have looped through all replacement pairs in the list. This is pretty negligible in terms of latency on a small dataset, but when you expand to larger and larger datasets, it could be a huge lag.

So today I present a recursive function that averts this issue. I used the @ scoping operator, which lets you recursively call functions.

The Code

With less fluff than yesterday, here is my code (Gist here):

let
  fnSwitch = (string as text, switches as list, default as any) =>
    let
      switch = (i) =>
        // if the string matches the first item in the list
        if string = switches{i}{0} then
        // then grab the second item in that nested list
          switches{i}{1}
        // make sure you don't exceed the max index
        else if i < List.Count(switches) - 1 then
        // if you are under the max index then recurse
          @switch(i + 1)
        else
        // set it to the default replacement value
          default, 
      // run the nested function
      result = switch(0)
    in
      result, 
  // Documentation
  fnType = type function (string as text, switches as list, default as text) as any
    meta [
      Documentation.Name = "fnSwitch", 
      Documentation.LongDescription
        = "This function takes a string and performs a SWITCH replacement using nested lists. Additionally, it provides a default value if there are no matches."
    ]
in
  Value.ReplaceType(fnSwitch, fnType)