The if-else version is a lot easier, but I really love how you explain the other version, that's really help to make us understand m language more deeply. Awesome content.
It's cool and clever, but I'd recommend sticking with the normal nested ifs as its more universally familiar. This technique, like much of custom M code, also breaks query folding which creates a different "scalability" problem if you have many records. As a final point (and this is only based on my experience), even ignoring query folding, it wouldn't surprise me if the nested ifs was more performant as I've found that iterating list access can have a noticeable impact. If you have many conditionals, creating an additional table and left joining could provide the most scalability (for non-query folding).
Thank you Chandeep. What a fantastic technique. Until I discovered your content, I was concerned at how we might provide 'Certified' datasets because they still require the user to understand DAX in order to create their own 'self-service' measures and columns. You PQ SWITCH function is perfect for devs to create really rich 'certified' datasets that users can easily build with. I have a colleague who, after seeing the power of power Query through your techniques, has decided to work in PQ and not in SQL. You really change perceptions. You are also underpinning all of my own best practice solutions at the PQ level so, again, thank you.
I would approach this differently and have the conditions and commissions as a separate table which could be joined to your main table. That way you can keep it all dynamic rather than hard coding values into the advanced editor.
I just wanna say, thank you. I am learning so much from you. You explained everything so clearly and I am always suprise that something that looks so complicated before is now comprehensive thanks to your awesome power bi communication skills.
Really nice example and I need to thank you not for this particular video, but for everything you did around PQ. Because of you (and BI Gorilla) I started to see PQ almost the way Neo saw the matrix 😅 You won't see bunch of views from me on this account, as I mostly watch you from company account 😂, but I'm there and I'm always recommending you as PQ guru. Keep it coming.
Download the file ⬇ - goodly.co.in/switch-function-power-query The Magic of Working with Lists in Power Query - ruclips.net/video/90atXaUhBec/видео.htmlsi=x49Gh1lgJ-Kse79o Tackle even the most challenging data-cleaning problems. Check out the M Language course and push beyond the user interface ↗ - goodly.co.in/learn-m-powerquery/
Thanks. But if you don't need that so much, you can forget. My alternative: use "Column by Examples. It first starts with = but if you add more numbers at some point the "algortithm" displayst >=. You can then change some numbers in the formula. I don't know if it works all the time, but I just tried out. Looking forward to see your next video.
Largest complexity in either approach right now is the redundancy of writing each threshold level twice. With nested if and switch you can avoid this, since after first match the result is selected and that's it. Switch statement does not require, that only one condition is true. With list approach you must provide complete condition, so only one result would match.
Your solution is brilliant and the way you make the explanation simple is amazing. When you were starting to present the solution, I thought you were going to suggest another solution which would be to create a function and call it within the query.
In a folder I've so many Excel files and want to load into power query in one go as different data like Sales, Payroll, Attandance, Returns, Product etc. here I just want to load and I don't want to combine it. Thanks a lot Goodly!!
you showed here also how to use something like index + match in excel in an easy way. I think this functionality is for me much more useful than swich. anyway great video, and as always very well explained :)
Great video! Curious if you could make this into a user defined function with three inputs: a list of values, list of conditions and list of results that then could be reusable, would be interesting to modify for a default value if no conditions are met.
Already splendid. Something really convenient in DAX Switch function is that the answer comes with the first TRUE answer. Would there be a tweak in this M code to give the position of the first TRUE in the list to even mimic better the DAX Switch? Maybe with a List.First ?
Hello, Is the processing for this "switch" function any faster or slower than a true nested if? Or is it all the same? I use very long nested ifs on millions of lines and was wondering sbout the impact on processing time.
Excellent video as usual, for a standard data set (int, text, etc) it’s great. I have to wonder if I can use the idea to test binary\table condition like Excel.Workbook(binarytotable) iserr true,false result being switch to ‘bad file’ or the normal [table]
This is amazing Chandeep, thanks for this. Is this somehow replicable in a way, that instead of hardcoding list, we can use some mapping file instead? Lets say, if another department is keeping excel with those commissions, and I am connecting this to my datamodel, I would like to automate this without me doing any maintenance of hardcoding
Great video…any technique that gets rid of nested ifs is a good one! Question: how would you incorporate an Excel table of the different commission levels to make this more end user friendly?
great video and clear instructions to use. Will check out your lists video too as I don't use them currently but think they will be incredibly valuable.
Hello Dear, is it possible to add conditional column in PQ based on parameters from multiple columns. I am asking since it is not working with me. Thanks
Nice take on this scenario, I like that it uses lists & table logic instead of nested ifs, feels a lot more like Power Query that way. But is it more readable or user-friendly?
Your videos are really helpful. I've been able to really improve my powerbi skills so thank you :D Just a quick question if that's okay. I have watched your videos on creating fiscal years but I can't figure out how to get it to show the fiscal period in a line graph. Can you help pls?
Great 2nd solution, thank you. However, I am a bit unsure why i do not get the first solution of cond. column…. Why wouldn’t it work to add a simple conditional column but using the rule upside down? Like (If greater then 14500 then 0.25 else if greater then 14000 then 0.20 else if greater then 12000 then 0.15…) and so on? Rather then using a condition with „greater then A but smaller than B“? I am sure there is a reason but I can’t see it…
Nice video. Wouldn't you run into an error if more than 1 condition is true? If so, is there a workaround for that? SWITCH only evaluates the first true (or false) condition and returns the result.
It's you. He's using a simple example to prove it works. For larger complex multi layered nested ifs, it makes sense to use this. My question: is it any faster or slower with millions of rows using this new way? Will it slow down any?
@roncruise4 Will probably slow down your query. With à nested-if, thou you perform n comparisons (n conditions), you only need to return 1 answer, this is a stored primitive type (boolean) and the query ends. This is as easy and low level as you can get. Using his method, you will need to to n comparisons and then store all these comparisons in memory as a list (no longer a primitive type, hence more space). You will them need to read the values from this list (list.positionof) and return the index. This here will slow your query. Reading from a list, even thou it is indexed, will still consume memory. And only after all of this can you return the index of the 2nd list, which is also stored in memory. Now all these operations and memory consumption for just a single row. Multiply that by m rows in your query, that will give you the space and time complexity of this method. I bet this is slower than multiple nested if statements, cuz they're primitive types and most languages including M, optimize for these operations. The only issue being to deal with the nested conditions. Heck, even a normal SWITCH statement in excel is just a glorified nested-if statement. There is elegance in simplicity. Hope it helped.
Hi, thank You very much for great content. I wonder what you said "something simillar to switch function" ;-) Maybe You thought about some functions with Your solution: fx_SWITCH( condition1, result1, condition2, result2, ... , else) ?
I still find "nested if" better in this case as it's adding too much of query folding. It is worth comparing how much time it takes in report refresh with a large dataset
Hi can you give me a solution why i'm not able to load xlsb file in powerquery window but same data i can load in xlsx format why it is happening.... in binary format file size reduce that's why we save file in binary format....
I am not sure this approach makes things easier here. On top, I see another performance issue on top of the query folding point mentioned below. In your example you have 4 conditions to check, and even if the first condition is already satisfied, your approach still calculates remaining ones. This is unnecessary and doesn't happen when standard if is used. Still, your video shows how great Power Query is :)
let Conditions = { [Amount] >= 1000 and [Amount] 10000 and [Amount] 14000 and [Amount] 20000 and [Amount] 30000 and [Amount] 50000 } , Boolean = List.Transform (Conditions , each if _ then 1 else 0), Results = {0.1,0.2,0.3,0.35,0.4,0.5} , ZipList = List.Zip ({Boolean,Results}) in List.Sum ( List.Transform ( ZipList,List.Product))
this is an awesome video. my below code worked well. however false conditions generates Error. let Conditions= { Text.Contains([Description],"STATIONERY"), Text.Contains([Description],"STATIONARY"), Text.Contains([Description],"FURNITURE"), Text.Contains([Description],"CHAIR"), Text.Contains([Description],"A4"), Text.Contains([Description],"HSE"), Text.Contains([Description],"GIFT") }, Results = {"CORPORATE","CORPORATE","CORPORATE","CORPORATE","CORPORATE","CORPORATE","CORPORATE"} in Results{List.PositionOf(Conditions,true)}
@@GoodlyChandeep noted. however, I changed my approach. in my case "if/or/then/else if and else" works better and easy. well, your videos are very informative and saves my time
for anyone wanting to watch this video it s useless information about how to complicate a simple nested if statement into a really complex and really over complicated List statement and combine 2 lists and pick up from the lists whatever you need based on conditions and etc etc etc useless
I have been using this since 2016 with the 1st version of "M is for (Data) Monkey. You define a simple function "fnSWITCH_HISTORIC" with your result/return pair combinations which you call from your query to the function. --------------------------------------------------------------------------------------------------------------------- (input) => // values A to L are items of [HERITAGE_BUILD_RIGHT] // translated to accepted historical designations. let values = { {"A", "NHLI"}, // National Historic Landmark, Individual {"B", "NRLI"}, // NRH Pics Lstd, Individual {"C", "NREI"}, // National Register Eligible, Individual {"D", "NCE"}, // Non-Con element Historic Property {"E", "DNE"}, // Determined not Eligible for List {"F", "NEV"}, // Not Evaluated {"G", "DNR"}, // Designation Rescinded {"H", "NHLC"}, // NHL Contributing Element {"I", "NRLC"}, // NRH Pics Lstd Contributing Element {"J", "NREC"}, // NRE Contributing Element {"K", "ELPA"}, // Eligible for purpose of PRG ALT {"L", "NAR"}, // Not Assessed Routinely {input, "UNKNOWN"} }, Result = List.First(List.Select(values, each _{0}=input)) {1} in Result ----------------------------------------------------------------------------------------------------------------- I then return one of the results: if fnSWITCH([HIST_Cd] = "A", returns to the calling query "National Historic Landmark, Individual". Simple enough.
![Blox Fruits Dragon Rework Update [Full Stream]](http://i.ytimg.com/vi/EqDAp8udhm0/mqdefault.jpg)
The if-else version is a lot easier, but I really love how you explain the other version, that's really help to make us understand m language more deeply. Awesome content.
You make M so easy to understand. The way you simplify and explain the concepts is just amazing. Thanks for sharing
It's cool and clever, but I'd recommend sticking with the normal nested ifs as its more universally familiar. This technique, like much of custom M code, also breaks query folding which creates a different "scalability" problem if you have many records. As a final point (and this is only based on my experience), even ignoring query folding, it wouldn't surprise me if the nested ifs was more performant as I've found that iterating list access can have a noticeable impact. If you have many conditionals, creating an additional table and left joining could provide the most scalability (for non-query folding).
Yes, tried this "creating an additional table and left joining could provide the most scalability"
Thanks.
Amazing video. I Start seeing M as a standard programming language instead of thinking of Chinese characters, thanks to you!
You are such an inspiration to thousands who are learning power bi..god bless you sir..!
Excellent video!
I watched this last night and forgot to comment. But I had so many ideas from watching this video!!
Thank you Chandeep. What a fantastic technique. Until I discovered your content, I was concerned at how we might provide 'Certified' datasets because they still require the user to understand DAX in order to create their own 'self-service' measures and columns. You PQ SWITCH function is perfect for devs to create really rich 'certified' datasets that users can easily build with. I have a colleague who, after seeing the power of power Query through your techniques, has decided to work in PQ and not in SQL. You really change perceptions. You are also underpinning all of my own best practice solutions at the PQ level so, again, thank you.
Except with large datasets 😊
@@txreal2that is true but with pq you need to be very cautious where to apply which resource intensive transformation step
I would approach this differently and have the conditions and commissions as a separate table which could be joined to your main table. That way you can keep it all dynamic rather than hard coding values into the advanced editor.
I just wanna say, thank you. I am learning so much from you. You explained everything so clearly and I am always suprise that something that looks so complicated before is now comprehensive thanks to your awesome power bi communication skills.
That's very kind of you to say! 🙏
💥💥 Nice one, and thanks for sharing. Not convinced it is "easier" than a conditional column though...
Thanks
Really nice example and I need to thank you not for this particular video, but for everything you did around PQ.
Because of you (and BI Gorilla) I started to see PQ almost the way Neo saw the matrix 😅
You won't see bunch of views from me on this account, as I mostly watch you from company account 😂, but I'm there and I'm always recommending you as PQ guru.
Keep it coming.
Download the file ⬇ - goodly.co.in/switch-function-power-query
The Magic of Working with Lists in Power Query - ruclips.net/video/90atXaUhBec/видео.htmlsi=x49Gh1lgJ-Kse79o
Tackle even the most challenging data-cleaning problems. Check out the M Language course and push beyond the user interface ↗ - goodly.co.in/learn-m-powerquery/
Thanks. But if you don't need that so much, you can forget. My alternative: use "Column by Examples. It first starts with = but if you add more numbers at some point the "algortithm" displayst >=. You can then change some numbers in the formula. I don't know if it works all the time, but I just tried out. Looking forward to see your next video.
Very good explanation and nice tricks. Thank you for the video! ❤
I was unable to see the importance or efficiency of this approach over nested ifs. Explain please?
me too. I tried to import a table of conditions and results, in order to make this more dinamic, but I failed :-( so, I do prefer nested if
Largest complexity in either approach right now is the redundancy of writing each threshold level twice. With nested if and switch you can avoid this, since after first match the result is selected and that's it. Switch statement does not require, that only one condition is true. With list approach you must provide complete condition, so only one result would match.
Watched this video almost immediately after it was posted and today I needed this exact thing. Once again: Thanks Chandeep! Awesome content :)
Awesome! 😄
Your solution is brilliant and the way you make the explanation simple is amazing.
When you were starting to present the solution, I thought you were going to suggest another solution which would be to create a function and call it within the query.
In a folder I've so many Excel files and want to load into power query in one go as different data like Sales, Payroll, Attandance, Returns, Product etc. here I just want to load and I don't want to combine it.
Thanks a lot Goodly!!
Not a problem in the first place
I see that in a lot of your videos
I would say, its a gr8 solution, i hv been in situations and used many nested if, now thankful 🙏 for you. 🎉
Error alert: added 6 results for 5 conditions. Anyways great stuff.
Brilliant! How do you make it dynamic if the conditions change just as the discounts would equally change?
Thanks for your videos, Chandeep. Is there any performance improvement with this approach?
you showed here also how to use something like index + match in excel in an easy way. I think this functionality is for me much more useful than swich.
anyway great video, and as always very well explained :)
Отличный кейс. Спасибо. 👍👍
This is neat! Thank you. Power Query M is awesome 😎
Nice one 👍
One list lookup another list based on true condition.
Great video! Curious if you could make this into a user defined function with three inputs: a list of values, list of conditions and list of results that then could be reusable, would be interesting to modify for a default value if no conditions are met.
Great work! M lists are so powerful. Thank you.
What a great video, great explanation and example, thank you
its Amazing the potental of powerquery, regards from Colombia
Already splendid. Something really convenient in DAX Switch function is that the answer comes with the first TRUE answer. Would there be a tweak in this M code to give the position of the first TRUE in the list to even mimic better the DAX Switch? Maybe with a List.First ?
Hello,
Is the processing for this "switch" function any faster or slower than a true nested if?
Or is it all the same?
I use very long nested ifs on millions of lines and was wondering sbout the impact on processing time.
Thank you for the wonderful explanation. I already have few use cases in mind while watching. 😀
Excellent video as usual, for a standard data set (int, text, etc) it’s great. I have to wonder if I can use the idea to test binary\table condition like Excel.Workbook(binarytotable) iserr true,false result being switch to ‘bad file’ or the normal [table]
creative one. thx
This is amazing Chandeep, thanks for this. Is this somehow replicable in a way, that instead of hardcoding list, we can use some mapping file instead? Lets say, if another department is keeping excel with those commissions, and I am connecting this to my datamodel, I would like to automate this without me doing any maintenance of hardcoding
Amazing! Thanks Chandeep!!!
Perfect! Awesome solution!
Great video…any technique that gets rid of nested ifs is a good one!
Question: how would you incorporate an Excel table of the different commission levels to make this more end user friendly?
great video and clear instructions to use. Will check out your lists video too as I don't use them currently but think they will be incredibly valuable.
Your video was very helpful, how can I apply this function if I'm using dates and in between and after specific date?
Very Nice and Incredible Video Bro..
Awsome and brillant use of lists.
Hello Dear, is it possible to add conditional column in PQ based on parameters from multiple columns. I am asking since it is not working with me. Thanks
see this - stackoverflow.com/questions/31548135/power-query-transform-a-column-based-on-another-column
@@GoodlyChandeep thanks for the feedback. problem resolved.
Nice take on this scenario, I like that it uses lists & table logic instead of nested ifs, feels a lot more like Power Query that way. But is it more readable or user-friendly?
Great video, Does it process data faster than a nested if statement?
Awesome Use case 🤟 Goodly Rocks 👏
Can we get by multiplying the two list conditions and Result and sum them?????
You are amazing, thank you for sharing.
As a beginner, can’t see the benefit of this convoluted solution. But a a learning anyway, thank you.
Does the length of the both lists, need to be same? Means 1st contains 5 rows, and 2nd list 3 rows
Yes
Very nice - many thanks!
How can I do this in DAX looking between tables?
Brillante ❤ Keep up the hard work my friend✌
Would love to see that as a function!
Awesome video as usual!
Does this have any impact over processing speed? Amazing video once again!
Your videos are really helpful. I've been able to really improve my powerbi skills so thank you :D
Just a quick question if that's okay. I have watched your videos on creating fiscal years but I can't figure out how to get it to show the fiscal period in a line graph. Can you help pls?
Is there a way to have wildcards in your conditions? So a=1200 and b>4000, a=1200 and b=%
Really nice! Tnx
Great 2nd solution, thank you. However, I am a bit unsure why i do not get the first solution of cond. column…. Why wouldn’t it work to add a simple conditional column but using the rule upside down? Like (If greater then 14500 then 0.25 else if greater then 14000 then 0.20 else if greater then 12000 then 0.15…) and so on? Rather then using a condition with „greater then A but smaller than B“? I am sure there is a reason but I can’t see it…
Nice video. Wouldn't you run into an error if more than 1 condition is true? If so, is there a workaround for that? SWITCH only evaluates the first true (or false) condition and returns the result.
Can you please show me to create a custom WE column using power query. I want start date of week should be Saturday.
is it just me, or anyone else think the NestedIF will just do fine here
Probably depends on the scenario
It's you. He's using a simple example to prove it works. For larger complex multi layered nested ifs, it makes sense to use this.
My question: is it any faster or slower with millions of rows using this new way? Will it slow down any?
@roncruise4 Will probably slow down your query.
With à nested-if, thou you perform n comparisons (n conditions), you only need to return 1 answer, this is a stored primitive type (boolean) and the query ends. This is as easy and low level as you can get.
Using his method, you will need to to n comparisons and then store all these comparisons in memory as a list (no longer a primitive type, hence more space).
You will them need to read the values from this list (list.positionof) and return the index. This here will slow your query. Reading from a list, even thou it is indexed, will still consume memory.
And only after all of this can you return the index of the 2nd list, which is also stored in memory.
Now all these operations and memory consumption for just a single row. Multiply that by m rows in your query, that will give you the space and time complexity of this method.
I bet this is slower than multiple nested if statements, cuz they're primitive types and most languages including M, optimize for these operations. The only issue being to deal with the nested conditions. Heck, even a normal SWITCH statement in excel is just a glorified nested-if statement.
There is elegance in simplicity.
Hope it helped.
Hi, thank You very much for great content. I wonder what you said "something simillar to switch function" ;-)
Maybe You thought about some functions with Your solution:
fx_SWITCH( condition1, result1, condition2, result2, ... , else) ?
2:23 Not sure but I guess the cases below 10000 would result in 25% commission, wouldn't it? I guess this was not the intension.
Amazing...❤
I still find "nested if" better in this case as it's adding too much of query folding. It is worth comparing how much time it takes in report refresh with a large dataset
🎉 creative 💡
Nice 🙏
Thank you
Hi can you give me a solution why i'm not able to load xlsb file in powerquery window but same data i can load in xlsx format why it is happening.... in binary format file size reduce that's why we save file in binary format....
Power query uses only xlsx file.
Nice as always :)
I am not sure this approach makes things easier here. On top, I see another performance issue on top of the query folding point mentioned below. In your example you have 4 conditions to check, and even if the first condition is already satisfied, your approach still calculates remaining ones. This is unnecessary and doesn't happen when standard if is used.
Still, your video shows how great Power Query is :)
Amazing.
Brilliant
I find nested if statements very easy to use in m code
Awesome!
Super !!!
I like this trick, Lot I use nested if. I will replace this trick
First formula is more simplier.😄
True at the first instance. One could argue that.
@@GoodlyChandeep but your example is very good for custom functions explanation.🤗
Full video on new updates on Dax, power query and pivot
The catch all (*) condition is missing
let
Conditions =
{ [Amount] >= 1000 and [Amount] 10000 and [Amount] 14000 and [Amount] 20000 and [Amount] 30000 and [Amount] 50000
} ,
Boolean = List.Transform (Conditions , each if _ then 1 else 0),
Results = {0.1,0.2,0.3,0.35,0.4,0.5} ,
ZipList = List.Zip ({Boolean,Results})
in
List.Sum ( List.Transform ( ZipList,List.Product))
❤
how cool was that
Did you add the extra value to the second list just to see if anybody would comment on it? The last .35 has no real purpose...
this is an awesome video. my below code worked well. however false conditions generates Error.
let
Conditions=
{
Text.Contains([Description],"STATIONERY"),
Text.Contains([Description],"STATIONARY"),
Text.Contains([Description],"FURNITURE"),
Text.Contains([Description],"CHAIR"),
Text.Contains([Description],"A4"),
Text.Contains([Description],"HSE"),
Text.Contains([Description],"GIFT")
},
Results = {"CORPORATE","CORPORATE","CORPORATE","CORPORATE","CORPORATE","CORPORATE","CORPORATE"}
in
Results{List.PositionOf(Conditions,true)}
The conditions need to be mutually exclusive
@@GoodlyChandeep noted. however, I changed my approach. in my case "if/or/then/else if and else" works better and easy. well, your videos are very informative and saves my time
I thought you were going to zip them, I have a bit of an obsession with list zip.
That would have produced a nested list. Hard to preview 😕
I will delete all my queries and follow some from your videos lol
Create video on febric pls
Really this is an easy version than nested if???😂😂
for anyone wanting to watch this video
it s useless information about how to complicate a simple nested if statement into a really complex and really over complicated List statement and combine 2 lists and pick up from the lists whatever you need based on conditions and etc etc etc
useless
I have been using this since 2016 with the 1st version of "M is for (Data) Monkey. You define a simple function "fnSWITCH_HISTORIC" with your result/return pair combinations which you call from your query to the function.
---------------------------------------------------------------------------------------------------------------------
(input) =>
// values A to L are items of [HERITAGE_BUILD_RIGHT]
// translated to accepted historical designations.
let
values = {
{"A", "NHLI"}, // National Historic Landmark, Individual
{"B", "NRLI"}, // NRH Pics Lstd, Individual
{"C", "NREI"}, // National Register Eligible, Individual
{"D", "NCE"}, // Non-Con element Historic Property
{"E", "DNE"}, // Determined not Eligible for List
{"F", "NEV"}, // Not Evaluated
{"G", "DNR"}, // Designation Rescinded
{"H", "NHLC"}, // NHL Contributing Element
{"I", "NRLC"}, // NRH Pics Lstd Contributing Element
{"J", "NREC"}, // NRE Contributing Element
{"K", "ELPA"}, // Eligible for purpose of PRG ALT
{"L", "NAR"}, // Not Assessed Routinely
{input, "UNKNOWN"}
},
Result = List.First(List.Select(values, each _{0}=input)) {1}
in
Result
-----------------------------------------------------------------------------------------------------------------
I then return one of the results: if fnSWITCH([HIST_Cd] = "A", returns to the calling query "National Historic Landmark, Individual". Simple enough.
Great content as always!
Is there a way you can use list.anytrue or list.alltrue for this?