Mimic a SWITCH function in Power Query using Record.FieldOrDefault
ฝัง
- เผยแพร่เมื่อ 28 มิ.ย. 2024
- 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
th-cam.com/users/bigorilla?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
![อาจารย์ยอด : ลุงแว่วเล่าเรื่องผี [ผี]](http://i.ytimg.com/vi/Fd2Qsy4dqpM/mqdefault.jpg)
The Definitive Guide to Power Query M is now available here: geni.us/ODZl8
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"}),
Definitely a much better approach than nesting IF statements.
This is great. Thank you! I have been wondering how to use a switch approach to power query as i love them :D
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
@@BIGorilla i am looking forward to the next video!
Uau!!! Que ótimo que você retornou! Seus vídeos mais antigos tem me ajudado muito! Que seu canal cresça muito! Parabéns!
❤ thank you!
I got your book which is great (i'am reading it). Great tuto too thanks 🎉
Incredible tutorial. Thank you very much.
Great technique, thanks a lot for sharing.
Glad you liked it!
Aweso(M)e, as always. Thanks for sharing.
Creative, good job i think that V2 Is better 😊 to avoide the hard coded
Brilliant thanks for sharing¡¡¡
this is really helpful how is the performance comparing with a traditional merge table?
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 :)
@@BIGorilla great!! Thank you
What a nice trick rick!!!
Once you know it, so straightforward too! Hope you learned something useful 😁!
Rick
Brilliant 👍
Thank you! Hope you find it useful 🙌
so... you re-invented left outer join, right? :)
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 :)
@@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!
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
Wow.
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?
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?)
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?
When will the book be available for purchase at Amazon ?
Hey Arne. The book is out since the start of this month 🙌. You can get it here: geni.us/ODZl8
💚
Very kind, thank you❤
@@BIGorillaHi sir thanks also. I bought your new book. It was awesome, I really learned a lot.
Woohooooooooo 🤞🙌