Mimic a SWITCH function in Power Query using Record.FieldOrDefault

Рет қаралды 4,980

Күн бұрын

The SWITCH function allows users to map input values to corresponding output values. In this video, you learn how to apply this concept in Power Query by using Record.FieldOrDefault
You can buy the Definitive Guide to Power Query M here: geni.us/ODZl8
WRITTEN ARTICLE:
gorilla.bi/power-query/switch/
Master Functions and Syntax in M
powerquery.how
ABOUT BI Gorilla:
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.
Website: gorilla.bi
SUBSCRIBE TO MY CHANNEL
kzbin.info?sub_con...
TIMESTAMPS
00:00 Introduction
00:19 The manual way
01:47 Understanding SWITCH
02:29 Using Record.FieldOrDefault
05:52 Taking a table as input
LET'S CONNECT:
Blog: gorilla.bi
Facebook: / bigorilla
Twitter: / rickmaurinus
LinkedIn: / rickmaurinus
Thank you for your support!
#switch #powerquery #bigorilla

Пікірлер: 38

@BIGorilla 3 ай бұрын

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 3 ай бұрын

Definitely a much better approach than nesting IF statements.

@amirmollaei3395 3 ай бұрын

Great technique, thanks a lot for sharing.

@BIGorilla 3 ай бұрын

Glad you liked it!

@g.oliveira1184 3 ай бұрын

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

@BIGorilla 3 ай бұрын

❤ thank you!

@Nikol_YouTube 3 ай бұрын

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

@BIGorilla 3 ай бұрын

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 3 ай бұрын

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

@ftmagicart 3 ай бұрын

Incredible tutorial. Thank you very much.

@raimundojs9547 3 ай бұрын

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

@baskis69 3 ай бұрын

Brilliant thanks for sharing¡¡¡

@eslamfahmy87 2 ай бұрын

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

@boissierepascal5755 3 ай бұрын

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

@anandabhaigade894 3 ай бұрын

What a nice trick rick!!!

@BIGorilla 3 ай бұрын

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

@kebincui 3 ай бұрын

Brilliant 👍

@BIGorilla 3 ай бұрын

Thank you! Hope you find it useful 🙌

@EciekPeciek 3 ай бұрын

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

@BIGorilla 3 ай бұрын

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 3 ай бұрын

@@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 2 ай бұрын

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

@RajKumar-hw4bt 3 ай бұрын

Wow.

@carloserodriguez2465 3 ай бұрын

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

@BIGorilla 3 ай бұрын

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 3 ай бұрын

@@BIGorilla great!! Thank you

@zolbootuvshin9519 2 ай бұрын

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?

@GeertDelmulle 3 ай бұрын

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 3 ай бұрын

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?