SWITCH Function in Power Query

Super !!!

Not a problem in the first place
I see that in a lot of your videos

Would love to see that as a function!

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 :)

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.

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 ?

Very Nice and Incredible Video Bro..

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 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?

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]

Brilliant! How do you make it dynamic if the conditions change just as the discounts would equally change?

Great content as always!

Is there a way you can use list.anytrue or list.alltrue for this?

This is neat! Thank you. Power Query M is awesome 😎

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).

Отличный кейс. Спасибо. 👍👍

Really nice! Tnx

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?

Thank you

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.

I like this trick, Lot I use nested if. I will replace this trick

Can you please show me to create a custom WE column using power query. I want start date of week should be Saturday.

Nice as always :)

Very nice - many thanks!

how cool was that

Very good explanation and nice tricks. Thank you for the video! ❤

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

Amazing...❤

Nice 🙏

The catch all (*) condition is missing

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)}

I would say, its a gr8 solution, i hv been in situations and used many nested if, now thankful 🙏 for you. 🎉

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 :)

Brilliant

Watched this video almost immediately after it was posted and today I needed this exact thing. Once again: Thanks Chandeep! Awesome content :)

I find nested if statements very easy to use in m code

As a beginner, can’t see the benefit of this convoluted solution. But a a learning anyway, thank you.

Your video was very helpful, how can I apply this function if I'm using dates and in between and after specific date?

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.

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

How can I do this in DAX looking between tables?

Thanks

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...

Thank you for the wonderful explanation. I already have few use cases in mind while watching. 😀

Really this is an easy version than nested if???😂😂

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 will delete all my queries and follow some from your videos lol

❤

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!!

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.