SWITCH() and Power Query – Part III: SWITCH() Harder

Live photo of me maintaining peak energy efficiency by switching off lights.

The saga continues. As I attempted to make the SWITCH() function in Power Query more efficient, I realized that I was probably overthinking things.

In our last episode, I left off with a recursive function that used the @ scoping operator. Then someone on LinkedIn (thanks Thomas!) pointed out that the scope parameter causes issues in dataflows (creates an error). I have to be honest, I remember doing some API stuff and webscraping in dataflows and crossed this issue, but I believe I never utilized the scoping operator in those fails, so this was news to me. Ack.

Back to the drawing board! Well, maybe not completely, because that version would work in Excel, but who really wants a function that only works certain places? One person does. Their name is Nobody!

Do I really need to loop?

So I started off the List.Generate() road and I was pretty far along, when I just up and deleted everything. Why? Because I thought maybe with list indices I could make it work. Then I did.

Here is what I came up with:

let
  fnSwitch = (string as text, switches as list, default as any) =>
    let
      select = try List.Select(switches, each _{0} = string){0}{1} otherwise default
    in
      select, 
  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)

How does this work?

Basically, I diverted to using List.Select() and checking the first item in the nested switches list. If there was a match, I would perform the switch. If there were no matches at all, an error would be thrown. So the try/otherwise catches the error and replaces it with a default value. That’s it. Easy peasy.

Again, is this the most elegant solution? Who knows. Did it work on 500K rows of data and did it perform well? Yes on both accounts.

Closing thoughts

The main thing with coding and figuring things out is that there are usually many ways to get the same answer. Agonizing over which solution is best can be counterproductive at times, especially when the arguments rely on theory as opposed to observations.

If you are just starting out, you will do inefficient things. That’s ok. I still do. Probably everyone does at some level. Gather experience and if a light bulb goes off and you enhance your own work later on, that’s a win.

Long story short: Don’t let the perfect be the enemy of the good and be good at perfecting your craft by continued learning.