SWITCH() with Power Query

SWITCH() is a monster of efficiency, but can it be used in Power Query?

I have used some version of a SWITCH statement in a variety of languages and applications. I remember utilizing something like this in VBA and Alteryx. SQL has the CASE statement, which does essentially the same thing. DAX has SWITCH(), but Power Query does not have a dedicated function.

If you are unfamiliar with the SWITCH() function in DAX, it basically evaluates a value and based on what the value is the SWITCH() function will return a corresponding value that you listed. If you did not provide a matching value, you can always define a default value for something like “other”.

There are so many applications for SWITCH() on the DAX side of the house. It can grease the wheels for a lot of dynamic functionality. I am not going to get into all of the use cases and if you want to learn more, start by checking out the article in DAX.Guide.

So, what is the actual point of this article? Well, I subscribe to Greg Deckler’s content on YouTube. I have been training newer folks on Power BI and have passed off his videos on DAX For Humans to provide them with different perspectives on how to write DAX. Often, their data model is really small (for Power BI) and they can struggle with the nuances of DAX and Power BI.

Greg posted a video today that challenged the idea of never using DAX Calculated Columns. Personally, I don’t use them too much at all, but sometimes have. Most of the time, I can get what I need with Power Query and write custom functions, but if my model is really, really small and a quick calculated column can help, I add one.

Here is Greg’s video:

In one part of Greg’s video, he pointed out that there was not an equivalent Power Query function for SWITCH(). He showed what most people (including me) do, which is write nested if statements.

Watching this I thought, how would you code a M function to do that? Then I coded one.

fnSwitch()

I will start out by saying that I don’t think I am the first to solve this problem at all. I am pretty sure some of the M experts out there have done this and probably have done it more efficiently. I am just sharing how I did it in case it helps.

The function takes 3 parameters:

• string: the text value being operated on
• switches: a nested list of values and replacements
• default: the default value if there are no matches

The interesting thing is that it returns any value. You can pass it a string to look for, but return an integer or decimal, for instance.

For demonstration purposes, I made a simple one-column table:

I added a custom column and typed the following:

In basic terms:

• I set the first parameter to the BASE column
• Second parameter was a nested list of key-value pairs. The first item in each nested list is like a “find”, the second item is the “replace”
• Third parameter was set to “Oranges”, which was default text in case of no matches

After hitting ok, I got this:

Seems to work. I used numbers as replacement values and that worked too.

Takeaways

Another, “I wonder if I could figure this out” exercise. I don’t believe it is the most elegant way to do this, since I used List.Accumulate() to run the loop and do not exit once there is a match. But I figure most people would not want to write too many find and replace nested lists, I don’t think there would be too much of a performance impact in most cases.

Also, just want to say that Greg’s videos are great and thought-provoking. Even if you don’t agree, it is helpful and healthy to see different perspectives on things.

The Code

Gist located here. Code pasted below.

let
  fnSwitch = (string as text, switches as list, default as any) =>
    let
      // Get max index of replacement list
      Max = List.Count(switches) - 1, 
      // Use List.Accumulate to loop through replacements
      Switch = List.Accumulate(
        switches, 
        default, 
        // if the string matches first index, then replace with second item in that nested list
        // else keep the default
        (state, current) => if string = current{0} then current{1} else state
      )
    in
      Switch, 

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