Although you can easily replicate the DAX SWITCH-function via list-, table- or record functions in M, I thought it would be convenient for many newbies to have a comfortable M-SWITCH-function that uses (almost) the same syntax than its DAX-equivalent:
SWITCH (
[Month],
1, “January”,
2, “February”,
3, “March”,
4, “April”,
5, “May”,
6, “June”,
7, “July”,
8, “August”,
9, “September”,
10, “October”,
11, “November”,
12, “December”,
“Unknown month number”
)
The DAX-SWITCH-function will retrieve the content of its first argument (expression) ([Month]) and check it against he first parameters of the following pairs (value). If there is a match, the second parameter of the pairs (result, here: month name) is returned and if there is no match, “Unknown month number” will be returned.
How it works
The syntax for the M-function looks like so:
M.Switch(Expression as any, Values as list, Results as list, optional Else as text)
So we have 4 parameters: The Expression just like in DAX, but then the Values and Results come as separate lists. The last optional argument is just similar to DAX again.
This allows for a very convenient entry of function parameters:
- You can quickly enter numerical ranges:
M.Switch(Month, {1..12}, {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}, "Unknown month number")
- You can super-easily refer to switch-values in tables:
It has just one minor flaw: When you refer to a parameter or another query in the Expression-field, you will be default get an error first. But removing the 2 quotes will quickly fix it:
This is because I’ve set the format of this field to “any”, as the condition can actually be of any type. But the function-dialogue has no way to handle different types currently and will transform all entries to text by default in that case.
It uses a technique that I’ve used in this article already: There you can see that the results can also be functions for example.
Function code
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| let func = | |
| (Expression as any, Values as list, Results as list, optional Else as any) => | |
| try Results{List.PositionOf(Values, Expression)} | |
| otherwise if Else = null then "Value not found" | |
| else Else | |
| , documentation = [ | |
| Documentation.Name = " M.Switch | |
| ", Documentation.Description = " Evaluates an <code>Expression</code> against a list of <code>Values</code> and returns one or multiple possible <code>Results</code> expressions. | |
| " , Documentation.LongDescription = " Evaluates an <code>Expression</code> against a list of <code>Values</code> and returns one or multiple possible <code>Results</code> expressions. | |
| ", Documentation.Category = " Other | |
| ", Documentation.Source = " https://wp.me/p6lgsG-Oa . | |
| ", Documentation.Author = " Imke Feldmann: www.TheBIccountant.com . | |
| ", Documentation.Examples = {[Description = " see: https://wp.me/p6lgsG-Oa . | |
| " , Code = " M.Switch(2, {1..3}, {""January"", ""February"", ""March""}, ""Unknown month number"") | |
| ", Result = " ""February"" | |
| "]}] | |
| in | |
| Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation)) | |
Most of the code is documentation (row 7 onwards) or handles the missing values: Row 5+6 will return the value from the optional 4th argument (Else) if used, otherwise the default-value: “Value not found” will be returned. The main function logic (in row 4) is the positional index indicator: {List.PositionOf(Values, Expression)} that is applied to the list of Results. List.OfPositions will return the position (number) of where the Expression has been found in the list of Values. That x-th value will then be picked from the list.
Enjoy & stay queryious 😉
Nice Imke, thanks for sharing. I can’t wait until M Intellisense is delivered by MS. All of these things will be so much more accessible to us all.
Hi Imke.
Why “optional Else as text”, not “optional Else as any”?
This is actually nice and fast version, thanks!!! Unfortunately, this function cannot mimic all the behavior of SWITCH, specially in the case of SWITCH(TRUE(),…) – it then need to evaluate an expressions in the Values… much more compex
Cheers, Maxim
Good point Maxim, haven’t thought about that format – changed it to any now 🙂
I will cover the SWITCH(TRUE.. and some even more awesome scenarios in my next article 😉
Nice! Can’t wait, it is very interestiong how you’ll solve it
Cannot wait to finish the article, as it’s one of the coolest things I’ve developed so far 😉
Hello,
Thanks for the great tutorial. But i have a suggestion if you wouldn’t mind. Wouldn’t the custom function be simpler if written as:
let func =
(Expression as number, Results as list, optional Else as any) =>
try Results{Expression-1}
otherwise if Else = null then “Value not found”
else Else
…
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))
The function simply accesses the value from a list based on index position. Let me know if I am missing something.
Thanks,
yes, your solution is more compact for cases where the expression comes from a numbered sequence, starting with 1.
But my solution is more general and would pick up lookups like this as well: {{“Jan”, “January”}, {“Feb”, “February”}…} where you put in “Jan” and get back “January” for example.
Can i see a worked example where this function is used to replace an existing list of values with values from another “switch” table?
Not sure I understand what you mean. Could you send link to worksheet with desired before and after?
Hi,
If I were to use the similar structure to create a function that takes multiple columns as arguments (Similar to a DAX SWITCH (TRUE(), Exp1a && Exp1b, “Value1”)…..how would I go about modifying the above function.
Thanks,
F
You would have to write out the first list argument manually. Instead of currently {1..12} it would be something like {“Exp1a” & “Exp1b”, “Exp2a” & “Exp2b”, … “Exp12a” & “Exp12b”}
Make sure just to use 1 ampersand (“&”) for the concatenation in M.
/Imke
Hi,
Where should I enter the formula M.Switch(Month, {1.. ? When I try to do it in the formula area when adding a new column it does not recognize M.Switch as a valid Power Query function?
M.Switch stands for the name of the function that you have to copy (the code you find in the blogpost).
So whatever name you gave it, replace “M.Switch” with its name (name of the query).
Hi Imke, you replied to Maxim you were going to post on the solution to SWITCH(TRUE() approach. I searched but did not find it on the blog. Did you get a chance to work through that solution?
Cheers
Phil
Hi Phil,
sorry, this is still on my list.
What would be your use case for it?
/Imke
I want to replicate the business logic from a Finance system report in PQ, to enable reporting from the Data Warehouse, where the logic does not yet exist.
Below is the code from the report (Tech1 Finance is the system) and also my attempt at a long if statement (yet to be tested as do not have the data yet).
Was interested to see if there is an approach that might work more like the SWITCH TRUE() function in DAX.
Thanks for any assistance, but am ok if you do not have time, as think can work through the if statement approach.
Cheers
Phil
Original Code from Finance system report:
Budget_Class__L3_ = ICAA
Budget_Class__L3_ = ICAC
Budget_Class__L3_ = ICAB
Budget_Category__L2_ one of ICC, ICE
Budget_Class__L3_ = ‘ICFA’ AND Natural_Account not one of (13005, 13006)
Budget_Class__L3_ one of (ICFB,ICFC,ICFD,ICGA) OR Budget_Group__L4_ = ‘ICFE100’
Budget_Group__L4_ = ‘ICFE200’ OR Natural_Account one of (13005, 13006) OR Budget_Class__L3_ = ‘IDAA’
Account_Type between (‘1’ AND ‘3’) AND Bud_Ctr___Discipline not one of (104,322,709,713,779,820,821,822) AND Section___School <> ‘4640’ AND Fund_Source = ’91’
Budget_Type__L1_ between IA IZ
Account_Type between (‘1’ AND ‘3’) AND Bud_Ctr___Discipline not one of (104,322,709,713,779,820,821,822) AND Section___School <> ‘4640’ AND Fund_Src_Budget_Cat between (‘BUD_018’ AND ‘BUD_025’)
Budget_Type__L1_ one of (IA, IB, ID) AND Budget_Class__L3_ not one of (IDAA) AND Natural_Account not one of (18416)
Natural_Account = 18416
Potential if Statement in Power Query:
if Budget_Class__L3_ = ICAA then “Australian Research Council” else
if Budget_Class__L3_ = ICAC then “National Health & Medical Research Council” else
if Budget_Class__L3_ = ICAB then “Other Australian Govt Funding” else
if Budget_Category__L2_ = ICC or Budget_Category__L2_ = ICE then “Tasmanian Govt Funding” else
if Budget_Class__L3_ = ‘ICFA’ and (Natural_Account <> 13005 and Natural_Account <> 13006) then “Other Contracts and Consultancies – Industry” else
if (Budget_Class__L3_ =ICFB or Budget_Class__L3_=ICFD or Budget_Class__L3_=ICGA) or Budget_Group__L4_ = ‘ICFE100’ then “Other Contracts and Consultancies – Other” else
if Budget_Group__L4_ = ‘ICFE200’ or (Natural_Account=13005 or Natural_Account=13006) or Budget_Class__L3_ = ‘IDAA’ then “Philanthropic Income, Donations & Bequests” else
if Fund_Source = ’91’ and (Budget_Type__L1_ >= “IA” and Budget_Type__L1_ <= “IZ”) then “IMOS” else
if (Budget_Type__L1_ = “IA” or Budget_Type__L1_ = “IB” or Budget_Type__L1_ = “ID”) and (Budget_Class__L3_ <>”IDAA” and Natural_Account <> 18416 then “Other”
if Natural_Account = 18416 then “Unknown Research Income” else “Not Found”
else “Exclude”
Thanks!
Let me check and I will come back.
Thanks Imke. I received the data today and they have already implemented the logic I needed, so I no longer need an approach.
That said, would be interested if you do come up with something as I feel it would be a useful tool to have, but for my needs, I can wait until you get time for that blog post.
Thanks for being prepared to have a look at this for me. Appreciate it, and the other help you have provided me on the Power BI Community as well.
Cheers
Phil
Hi Imake,
i have scenario like this
case when table1.status = ‘ACTIVATED’ and table2.callingparty is not null then ‘XYZ’
when table1.status = ‘ACTIVATED’ and table2.callingparty is null and table3.callingparty is not null then ‘ABC’
how can i add a new column for this
I’m sad that the images are broken! Could you update the images?
Pingback: Switch – Beyond Basic Power BI
Pingback: SWITCH and SWITCH(TRUE... in Power Query – XXL BI