Creating a SWITCH Function with Conditions in Power Query M

[@BIGorilla]

Now available: 'The Definitive Guide to Power Query M'. You can get it here: geni.us/ODZl8

[@PowerBIPark]

Okay this is super embarassing - I've seen so many of your videos but I wasn't subscribed 💀💀 I just have you on bell on linkedin instead xD Great to see you posting again regularly :)

[@BIGorilla]

Good to see you here too Injae! New subscriber for me, yeeea 🙌

[@BrianBnk]

You are tooooo wild in M. Love it 😊

[@BIGorilla]

Mr Brian - it's been a while! I'm really glad to read you're liking the M code. More is coming for sure 🙌

[@adrianoschwenkberg6773]

Hello Rick, it becomes like a challege how many methods we will find to replicate the switch function using M. Did you consider this one already, this is my approach? let source = "C", tbl_switchvalues = Table.FromRows({{"A","a"},{"B","b"},{"C","c"}},{"from_val","to_val"}), switch_return = try tbl_switchvalues[to_val]{List.PositionOf(tbl_switchvalues[from_val],source)} otherwise "default value" in switch_return

[@williamarthur4801]

Hi, enjoyed the linked in earlier, I've been working on this; ( alist as list, N as number)=> let S = let listF = { List.Sum, List.Average, List.Max, List.Min} in Function.Invoke( listF { N } , { alist } ) in S I've tried other functions, Table / add/ remove, not entirely successful, but you might find it worth playing around with. I wanted fo feed invoke a Reocrd field value, but does not seem to like them?

[@ПавелЛинев-ч7м]

Top

[@YTNutflexPayb]

❤

[@BIGorilla]

❤

[@LouisDeconinck]

Does Microsoft sometimes add new functions to M? Switch would be a good candidate.

[@BIGorilla]

Hey Louis - Yes they do! So who knows in the future this may be one of the candidates. As long as they add Query Folding it would be fantastic!

[@GeertDelmulle]

Just a few thoughts: • I’m sure this is the easiest way to do this… • I like the syntax of the list in the Table.TransformColumns function and would be inclined to go that route. (Either way: SWITCH and IFS are more efficient than this: a soon as a condition is met, the sweeping the tests stops: here all conditions are always evaluated) Queries where all conditions are not evaluated all the time are a little more difficult to generate. ;-) Q: Can you do an approximate match lookup in Power Query? How would you approach that? Thanks in advance.

[@BIGorilla]

Hey Geert, Glad to see you back on the channel. The intention of the video was never to provide the easiest or more efficient way to do this. It's a way to replicate the SWITCH function (and learn some M on the way). - I agree SWITCH or IFS is more efficient. However, I don't share the opinion that all conditions are always evaluated here. Remember that in the M language, lists, records and table member expressions are evaluated using lazy evaluation. Here are some sources describe this in more detail: learn.microsoft.com/en-us/powerquery-m/evaluation-model bengribaudo.com/blog/2023/03/03/7292/lazy-streamed-immutable-try-building-a-table - In response to: "Queries where all conditions are not evluated all the time are a little more difficult to generate": We could resort to regular if-statements if you don't want all conditions to be evaluated. Likewise, using lists to store conditions uses lazy evaluation, and does not necessarily evaluate each item. On your question for the approximate match lookup: What scenario would you want to learn about approximate match? Pattern matching for text? Or do you want to use table with threshold values (like bonus % ) that are applicable between 0-5000, 5000-10000, 10000-20000? Rick

[@robertbachman9521]

@@BIGorilla Geert, I too would like to know about approximate matching in PowerQuery. I am using PowerQuery in Excel and would love to have something like the Xlookup function whereby it would choose the value less than or equal in the x value range, to give the y value. I am an engineer and have a lot of separate time series data files in the following format (yyyy-mm-dd hh:mm:ss) that I need to merge. For my application I am looking at pressure data at different physical locations. Usually the data is in 1 second increments, but different gauges may have different granularity of data. There is no guarantee of an exact match in time across the various files.

[@mecorowa5642]

@@robertbachman9521 Hi, I am struggling with the same issue. Excel / Power query can become very slow with thus huge data sets. Then I let ChatGPT to write a python script. It does the same calculation or other operations in seconds.

[@mienzillaz]

= (pItem as text, pCondition as text, pResults as text, pFallback as text)=> let Source = [cond = pCondition, res = pResults, fall = pFallback], conditions = List.Transform(Text.Split(Source[cond],"||"), each Text.Split(_, "|")), results = Text.Split(Source[res],"||"), fallback = Source[fall], combine = Table.ExpandListColumn(Table.FromColumns({conditions, results}, {"Condition", "Result"}), "Condition"), item = try combine{List.PositionOf(combine[Condition], pItem)}[Result] otherwise fallback in item