A generic SWITCH-function for the query editor in Power BI and Power Query

Although you can easily replicate the DAX SWITCH-function via list-, table- or record functions in M, I thought it would be convenient for many newbies to have a comfortable M-SWITCH-function that uses (almost) the same syntax than its DAX-equivalent:

SWITCH (
[Month],
    1“January”,
    2“February”,
    3“March”,
    4“April”,
    5“May”,
    6“June”,
    7“July”,
    8“August”,
    9“September”,
    10“October”,
    11“November”,
    12“December”,
    “Unknown month number”
)
DAX Formatter by SQLBI

The DAX-SWITCH-function will retrieve the content of its first argument (expression) ([Month]) and check it against he first parameters of the following pairs (value). If there is a match, the second parameter of the pairs (result, here: month name) is returned and if there is no match, “Unknown month number” will be returned.

How it works

The syntax for the M-function looks like so:

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

So we have 4 parameters: The Expression just like in DAX, but then the Values and Results come as separate lists. The last optional argument is just similar to DAX again.

This allows for a very convenient entry of function parameters:

1. You can quickly enter numerical ranges:

M.Switch(Month, {1..12}, {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}, "Unknown month number")

2. You can super-easily refer to switch-values in tables:

It has just one minor flaw: When you refer to a parameter or another query in the Expression-field, you will be default get an error first. But removing the 2 quotes will quickly fix it:

This is because I’ve set the format of this field to “any”, as the condition can actually be of any type. But the function-dialogue has no way to handle different types currently and will transform all entries to text by default in that case.

It uses a technique that I’ve used in this article already: There you can see that the results can also be functions for example.

Function code

Most of the code is documentation (row 7 onwards) or handles the missing values: Row 5+6 will return the value from the optional 4th argument (Else) if used, otherwise the default-value: “Value not found” will be returned. The main function logic (in row 4) is the positional index indicator: {List.PositionOf(Values, Expression)} that is applied to the list of Results. List.OfPositions will return the position (number) of where the Expression has been found in the list of Values. That x-th value will then be picked from the list.

Enjoy & stay queryious 😉

Comments (7) Write a comment

  1. Nice Imke, thanks for sharing. I can’t wait until M Intellisense is delivered by MS. All of these things will be so much more accessible to us all.

    Reply

  2. Hi Imke.
    Why “optional Else as text”, not “optional Else as any”?

    This is actually nice and fast version, thanks!!! Unfortunately, this function cannot mimic all the behavior of SWITCH, specially in the case of SWITCH(TRUE(),…) – it then need to evaluate an expressions in the Values… much more compex

    Cheers, Maxim

    Reply

    • Good point Maxim, haven’t thought about that format – changed it to any now 🙂
      I will cover the SWITCH(TRUE.. and some even more awesome scenarios in my next article 😉

      Reply

  3. Hello,

    Thanks for the great tutorial. But i have a suggestion if you wouldn’t mind. Wouldn’t the custom function be simpler if written as:

    let func =
    (Expression as number, Results as list, optional Else as any) =>

    try Results{Expression-1}
    otherwise if Else = null then “Value not found”
    else Else

    in
    Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

    The function simply accesses the value from a list based on index position. Let me know if I am missing something.

    Reply

    • Thanks,
      yes, your solution is more compact for cases where the expression comes from a numbered sequence, starting with 1.
      But my solution is more general and would pick up lookups like this as well: {{“Jan”, “January”}, {“Feb”, “February”}…} where you put in “Jan” and get back “January” for example.

      Reply

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz