Switch with Power Query: Final Boss Battle

I will admit that I have been a little preoccupied with this problem. This all started with me watching one of Greg Deckler’s videos. The larger point of the video was the countering of the “never use DAX calculated columns” mantras you may see (I use them – full admission). I agreed with the larger point and was happily entertained.

But then Greg talked about how Power Query does not have a SWITCH() function. I knew this to be true and have made plenty of ridiculous if/then/else statements in my day. But I wondered, could you do it?

Side Quest: Other Mashup Tools

I have used a bunch of different data mashup tools, for lack of a better way to categorize them. For a time I was certified in Alteryx Desktop. I’ve also used KNIME, OpenRefine, and Tableau Prep. I’ve also used VBA to do mashup things (before I learned Power Query). From this experience, I have one gripe that is related to something else I posted about.

Alteryx and KNIME, conceptually as applications, both have one major feature that is similar that extends their utility greatly. They both have a base set of tools to mash data up, but the tools can be packaged together to make other tools. Don’t have a specific tool in Alteryx that fits what you are trying to do? Can you connect 5 base tools to make what you want? Cool, package that all up as a custom tool and publish it in Alteryx Community. Anyone in the community can now profit off your generosity and ingenuity. KNIME has a community for this as well.

Power Query? There are a lot of people that use it. They also use it in different ways. Most use it in Power BI Desktop, but you can use it in Fabric, Dataverse, Excel, Power Automate and probably some other places I am forgetting. If you figure out a novel solution to a problem, and you post it somewhere where others can easily import and use? Nope.

In this vacuum of a collaborative mechanism to foster community sharing, M code is shared often, but there is no “bat signal” to follow to find it all. New users struggle, see M and its weirdness (to them), stay in the point-in-click part (if they get that far), and build things that are not optimal. And so it goes.

The Switch() statement fixation is stemming from the feeling that although we don’t have this mechanism, we should at least have a Switch() statement that mimics DAX.

Here is my final attempt.

The Code

Here is the Gist: SwitchTrue.pq (github.com)

The function takes 3 arguments: the value, a list of condition-result pairs, and a default value if nothing evaluates to true. In the second parameter, the condition should be a function, but syntax sugar can be used to make it easier.

Here is an example of calling it:

Table.AddColumn(base, "Switch", each Switch(
    [Measurement 1], 
    {
       // Using syntax sugar of each _ provides shorthand for a function
        each _ > 8, "GT8",
        each _ > 7, "GT7",
        each _ > 6, "GT6",
        each _ > 5, "GT5",
        each _ > 4, "GT4"
    },
    "LT4"
), type text)

When used as an ‘Add Column’ step, you can set the type as the return. I used ‘Any’ type in case folks want to pass in numbers or text to evaluate (or possibly even something else).

Wrap Up

I think this is probably the closest I’ve gotten to mimicking the DAX function. I’ve added it to the Power Query shared libary: https://github.com/cbaragao/PQ-Shared-Library.

If you also want a way to share M code like how other mashup tools go about it, consider voting for one of the many ideas listed in this other article I wrote on that subject: https://powergi.tech/power-query-idea-import-m-code-extensions-from-a-power-query-gallery/.

Hope it helps!