Mimic a SWITCH function in Power Query using Record.FieldOrDefault

[@BIGorilla]

The Definitive Guide to Power Query M is now available here: geni.us/ODZl8

[@XoFoKleX]

Hi Rick, amazing video, thanks for sharing! One way to reduce the steps if you were to delete the original column anyway: You can also use this in a Replace step so you don't necessarily have to add a new column. SWITCH_Step = Table.ReplaceValue ( Previous_Step, each [Column_where_you_want_to_use_SWITCH], each let switch_to = Record.FromTable ( Mapping_Table ), switch = Record.FieldOrDefault( switch_to, [Column_where_you_want_to_use_SWITCH], "MISSING") in switch, Replacer.ReplaceText,{"Column_where_you_want_to_use_SWITCH"}),

[@LouisDeconinck]

Definitely a much better approach than nesting IF statements.

[@carloserodriguez2465]

this is really helpful how is the performance comparing with a traditional merge table?

[@BIGorilla]

The transformation does not fold. So when working with a database; merging or using an if statement will be faster. However, sometimes you may not have a table to merge. This could be just little overhead for clean code. The choice is yours :)

[@carloserodriguez2465]

@@BIGorilla great!! Thank you

[@g.oliveira1184]

Uau!!! Que ótimo que você retornou! Seus vídeos mais antigos tem me ajudado muito! Que seu canal cresça muito! Parabéns!

[@BIGorilla]

❤ thank you!

[@Nikol_YouTube]

This is great. Thank you! I have been wondering how to use a switch approach to power query as i love them :D

[@BIGorilla]

Nikol, thank you. SWITCH has been a pleasure for conditional logic. This version helps remapping values, but it doesn't support the true value unfortunately. That's because the record field names need to be unique. So when conditions evaluate to true, the record approach would get an error. In my next video I show an alternative that supports conditions too. Keep an eye out if that's something you want to know more of! Rick

[@Nikol_YouTube]

@@BIGorilla i am looking forward to the next video!

[@ftmagicart]

Incredible tutorial. Thank you very much.

[@zolbootuvshin9519]

Hey thank you for video, really interesting and helpfull. If you don't mind could you help with these. In column Name when there are same values i get error "Expression.Error: The field '1' already exists in the record.Details: Name=1 Value=" Is there a way to fix it, or it must be made by other functions?

[@eslamfahmy87]

Creative, good job i think that V2 Is better 😊 to avoide the hard coded

[@gezin_longuevilleosaer4245]

hoi Rick, terug een erg mooie en bruikbare video. Ik moet echt meer in je boek duiken en in je website PQ how.

[@amirmollaei3395]

Great technique, thanks a lot for sharing.

[@BIGorilla]

Glad you liked it!

[@anandabhaigade894]

What a nice trick rick!!!

[@BIGorilla]

Once you know it, so straightforward too! Hope you learned something useful 😁! Rick

[@boissierepascal5755]

I got your book which is great (i'am reading it). Great tuto too thanks 🎉

[@raimundojs9547]

Aweso(M)e, as always. Thanks for sharing.

[@GeertDelmulle]

Hey Rick, Beautiful technique! But, can’t this be done via a Merge with that Lookup table? The nulls can be replaced by “Other”. And yes, I already purchased your book in PDF :-). Other question: how do you do approximate match lookup? Is that conditional? :-) (Do make sure you get enough sleep, OK?)

[@BIGorilla]

Hey Geert, Yes you can do such with a merge. But that’s not the point of this video. The point is that a switch statement is convenient for mapping values. And it’s not available in M. Could you use a merge? Yes. But what if you don’t have a separate table available and just need the values for an easy operation? The way described uses switch in that way. If you want to merge instead, also good. Performance wise? Maybe even better. So approximate match lookup. Do you mean a lookup for values where for instance tier 1 is values smaller than 10, tier 2 is values smaller than 1000, tier 3 smaller than 10.000?

[@kebincui]

Brilliant 👍

[@BIGorilla]

Thank you! Hope you find it useful 🙌

[@baskis69]

Brilliant thanks for sharing¡¡¡

[@EciekPeciek]

so... you re-invented left outer join, right? :)

[@BIGorilla]

Sort of! 😂 But also, SWITCH. Have you used it before? If I don’t have a table with the necessary values; it would be quite some coding to create a temporary table within the query, then left outer join a previous step with the latest one. In a way, the same goes for the switch function. You could left outer join. Or not if the multiple branches are only needed for the current context. Not the purpose of this video though. Purpose is to mimick switch and learn some M along the way :)

[@EciekPeciek]

​@@BIGorilla Thanks! I get it. Useful under some circumstances. I usually use merge but this SWITCH is very interesting approach too. And no... I was not using such a "SWITCH" before :) Only in DAX, very often. Not PQ. So, big thanks!

[@RichardJones73]

I find merge left join to be quite slow when refreshing. Would this be quicker I wonder? Also, I think I need to watch the next video for multiple column mapping

[@arne.munther]

When will the book be available for purchase at Amazon ?

[@BIGorilla]

Hey Arne. The book is out since the start of this month 🙌. You can get it here: geni.us/ODZl8

[@MUAISC]

Wow.

[@YTNutflexPayb]

💚

[@BIGorilla]

Very kind, thank you❤

[@YTNutflexPayb]

@@BIGorillaHi sir thanks also. I bought your new book. It was awesome, I really learned a lot.

[@Charlie888]

[@BIGorilla]

Woohooooooooo 🤞🙌