SWITCH and SWITCH(TRUE… in Power Query

Ever wanted to mimic the SWITCH function from DAX (or CASE ... WHEN from SQL) in Power Query, including the SWITCH(TRUE... behavior? In this blog post, I’m providing a function that does just that.

Background

There’s a great function in DAX called SWITCH; if you’re unfamiliar with it, I suggest you read the DAX Guide entry on it, since it provides several examples.

For the purposes of this blog post, I’d like to point out that there are two ways to use SWITCH:

1. Simple SWITCH
2. Searched SWITCH, more commonly known as SWITCH(TRUE...

Unfortunately, Power Query doesn’t have a built-in equivalent.

Existing solutions

Several people have already written their versions of DAX’s SWITCH in Power Query — Imke Feldmann perhaps most notably. As far as I understand from the comments, Imke’s version didn’t solve the SWITCH(TRUE... problem. Besides, I’m not fond of its syntax, since you need to enter values and results as separate lists:

M.Switch(Expression as any, Values as list, Results as list, optional Else as text)

Other implementations that I could find on the web were similar to Imke’s.

My solution

I wanted to mimic DAX’s syntax as closely as I could: I wanted to enter the value to check, then values and results, as well as the optional else result all in one go, not as separate parameters. To balance the complexity of the function and user-friendliness, I settled for one parameter of type list.

You can paste the following code as a blank query and call it Switch:

let
    // Created by Daniil Maslyuk
    Switch =
        let
            Function = (input as list) as any =>
                let
                    Source = List.Buffer(input),
                    Expression = List.First(Source),
                    SkipExpression = List.Skip(Source),
                    HasElse = Number.IsEven(List.Count(Source)),
                    ElseValue = if HasElse then List.Last(Source) else null,
                    ValuesResults = List.RemoveLastN(SkipExpression, Number.From(HasElse)),
                    Values = List.Alternate(ValuesResults, 1, 1, 1),
                    Results = List.Alternate(ValuesResults, 1, 1, 0),
                    FirstResult = List.PositionOf(Values, Expression),
                    FunctionResult = if FirstResult = -1 then ElseValue else Results{FirstResult}
                in
                    FunctionResult,
            FunctionType = type function (input as list) as any
                meta [
                    Documentation.Name = "Switch",
                    Documentation.LongDescription = "Evaluates an expression against a list of values and returns one of multiple possible result expressions.",
                    Documentation.Examples = {
                        [Description = "Simple Switch expression", Code = "Switch({2, 1, ""A"", 2, ""B""})", Result = """B"""],
                        [Description = "An equivalent of SWITCH(TRUE... in DAX", Code = "Switch({true, 1 > 2, ""A"", 1 < 2, ""B"", ""No result""})", Result = """B"""]
                    }
                ],
            TypedFunction = Value.ReplaceType(Function, FunctionType)
        in
            TypedFunction
in
    Switch

Below you can see a simple and searched Switch examples, respectively. Both output “B”:

Switch({
    2,
    1, "A",
    2, "B"
})

Switch({
    true,
    1 > 2, "A",
    1 < 2, "B",
    "No result"
})

Sample file: Switch in Power Query.pbix

Feel free to provide your feedback and happy switching! 🙂