SWITCH Function in Power Query

the switch function is absolutely brilliant for replacing the nested if statements and you can do that either in Excel you can do that in Dax but guess what for some reason the switch function is not available in power query H how ridiculous is that in this video I'm going to show you a technique which is where you can almost replicate the behavior of the switch function and not write those nested if statements all right you ready let's go check it out all right I'm in Excel power query here on one side I have the data loaded very simple data nothing that complicated we just have two columns the first one is the date the second one is the amount and based on the amount I would like to give some commission and here is the logic for commission now if you take a look at the logic the logic States something like this if the amount is in between 10 and 12,000 then give a 10% commission if the amount is between 12 and 14,000 then 15% the next band is 14 to 15 actually 14 to 14 and A2 that's 20% and anything anything above 142 is 25% Commission Now if you were to try to do this in power query you would have to write a nested if statement let's just take a look at how would that nested if statement look like all right to be able to write the nested if what you can do is you can go over to the add columns and you can create a conditional column that's one way of writing the nested if but it doesn't really give me the flexibility and the freedom of writing the and conditions and hence I would choose to write a custom column and write the if statement by myself now I'm going to call this as commission using nested if and my formula is going to be something like this so I will start off with an if statement and I'm going to say if the amount column is greater than equal to 10,000 and my amount column is less than equal to let's say 12,000 then I would give a 10% commission and the way to write that is 0.1 else then I will move on to the next line and I will start to write the next if now I'm going to quickly complete that and discuss the if statement with you all right so here is my if statement and what you can see is that I have written all the conditions and I have layered my if statements into three layers so here is the outmost if and then inside that there is another if and there is another if and that's my L statement although it's absolutely fine but it's kind of tedious to write if conditions this way you might as well miss a part of the if and it starts to give you error is there a better way of writing that perhaps using a switch statement yes there is at the moment I'm going to click on okay and confirm to this formula seeing that I do get the right result but let's just try to write that using a switch statement instead all right let's just start to create one more custom column and plug in the switch statement or something that probably looks like the switch statement not exactly the switch statement so I'm going to go over to the add columns Tab and create a custom column in the custom column I'm going to name that as commission using switch the very first thing that I'm going to do is I'm going to create a list and in the list I will write all my conditions and how do you create a list I'm sure you've taken a look at one of my past videos before if you want to initiate a list you have to start with a girly bracket if you haven't checked out the video on list exclusively I've done a video on that and you're going to find that incredibly helpful I'm going to leave a link and you should go watch that as well nevertheless lists to begin with so I will start the bracket a curly braces and I will close the curly braces and this is my blank list in the blank list I will based all my conditions now that's my first condition which is where amount is greater than equal to 10,000 and amount is less than equal to 12,000 after every single condition you have to separate that out with a comma that is the first condition or first item of the list the second condition or the second item of the list the third item and eventually the fourth item of the list now once you have done that what you can do is you can just simply click on okay and what you're going to get is nothing but a list and if you just peek into the list you can see that here I get a true and false now if you just take a look at the true and the false statement here in this list that we just created the second condition was true and therefore we get a true right here and if I take a look at the second one the first condition was true which is nothing but this particular condition the amount is actually between 10 and 12,000 that's the amount that I can take a look at and that's the reason why the first condition here gives you a true let's continue to write the formula ahead I'm going to go ahead and click on the gear icon open up the formula now from here on if I want to make things a tad bit easier I'll have to create variables so that I can store the list and other work that I'll have to do so I'm going to go ahead and start to write my let statement so I'll just say let and let's just say that the first list that I have created I'm going to give it a name call that list as my conditions and that is my first list now what I'm going to do is I'm going to create a second step put in a comma and you can create a second step and the second step is going to be results and what are my results my results are 10% % commission 15% commission 20% commission or 25% commission and again that is going to be in a list format so I'm just going to start to initiate a list with the curly braces and within which I will write those four numbers once I've written the four numbers down especially the results in the form of a list let's just close the let in the in Loop so I started this by the let and I'm going to close this with the in and I'm going to say that hey why don't you now show me the results so I have the results right here and if I click okay what I'm going to see is the four numbers as a list so I just click on okay if I preview the list I'm going to get to see the four numbers the 10% the 15% the 20% and the 25% now let's just play a trick to kind of visualize what's going to happen now so I'm just going to go ahead and instead of returning one list as an output which is the results list I'm going to start to return both the lists as an output just to help you visualize it we not kind of committing to the formula yet so I'm going to maybe use a function called list. combine because I'm trying to to combine or return two lists my first list is going to be my conditions list and my second list is going to be my results list and I'm going to make a list out of that so this and this is going to be in a list close that and click on okay now I still get a list but this time I have returned two lists not one so that was my first list which is where the second value was true and therefore I should now pick up this particular number and if I take a look at the second one here the first value is true and therefore I should pick up the first number right here how do I do that in power query now if you look closely this is some sort of a lookup that I have to do something like this take a look at this particular list why don't you find the position of the true true is on the first position and therefore you should pick up the first number from here now just go on to the next one hey take a look at this list find the position of true true is on the second position and you should then pick up the second number from here that's EX exactly what I'm going to do now how do I do that I'm just going to go back and start to edit my formula in the formula I'm going to say something like hey the final output is going to be from the list called results but I would like to pick up a particular number and the number is going to be a lookup value of the value true so I'm going to go ahead and write something like list. position off because I'm trying to find the position of the value true so list. position off and I'll start the bracket the first part is nothing but a list so I will feed my conditions list in the conditions list the value that I'm trying to find is the true value uh t r u e I'm going to close the bracket and I'm going to close the curly braces towards in the end now if you take a look at this what you're trying to say is that hey I have a conditions list which is which is nothing but this one in this one you why don't you find the value true and that is going to be the second value and once you get the two as an output of this particular list. positions function why don't you just pick up that number two off the results list simple as that now I'm going to click on okay and that is my output using something similar to a switch function although I do understand that the formula might look a tad bit complicated but it allows for scalability let's just take a look if I go back to the formula and I start to make a tweak in the condition you're going to see that how easy is that to edit this condition without getting into the Trap of nested divs and probably getting into errors so I'm just going to maybe delete this particular uh condition right here and I'm going to copy the previous row and I'm going to start to say that hey now this time I'm looking at numbers which are Beyond 14500 and the amount should be greater than 14600 less than 14600 and that is one condition and uh then I'm going to say hey I have one more condition amount greater than 14600 and those are my conditions and I'm just going to get rid of the end part now those are my conditions that I have written and now there are how many conditions we have 1 2 three four and five conditions and therefore also I'm going to have five outputs in here so I'm going to say 25% output and then 3 30% output and then I'm going to maybe have let's say a 35% output right that is nothing but my condition or my results and now as soon as I click on okay I'm going to start to see that the outputs do have changed for the conditions that I have had it it becomes so much simpler rather than wrangling around with the nested if statements all right that's been it let me know how did you find this trick of almost trying to replicate the switch Behavior but using lists instead if you have any questions around this I would be waiting for them in the comments below and before you go I'd like to give a big shout out about my tax and my power query and especially my M language courses these are the exact perhaps even more complicated problems we discuss in the course I teach you the logic first as to how do you think about a problem how do you construct the logic and then how do you start to solve difficult problems even of your own data by yourself if you're interested the link is in the description of the video thanks so much for watching and I'm going to catch you guys in the next one cheers bye [Music] now

Channel: Goodly

Views: 23,081

Keywords: Goodly, Chandeep chhabra, power query goodly, M language goodly, List in power query, Switch Function in power query, Nested if in power query, Conditional logic in Power Query, Nested IF statements in Power query, conditional column in power query, replace nested if in power query

Id: OPeg4ekdgrU

Length: 10min 24sec (624 seconds)

Published: Wed Mar 06 2024