I need to create a new column based on multiple conditions.
I have below code works but doest no give correct values.
what I want is:
when status is rejected the value is return reason
when status is Quality-Reject the value is Quality-Rejection Reason
when status is Quality-Reject and “Quality-Rejection Reason” is blank then the value is remarks
else is 0 as I dont need it.
if [Status] = "Rejected" then [Return Reason]
else if [Status] = "Quality-Reject" then [#"Quality-Rejection Reason"]
else if [Status] = "Quality-Reject" and [#"Quality-Rejection Reason"] = 0 then [Remarks]
else 0
the problem is that it doesnt see the “AND” condition.
It’s because if the status is Quality-Reject, the output will be #”Quality-Rejection Reason”. It will never reach the third situation.
maybe you can switch the second line and third line to have a try
if [Status] = "Rejected" then [Return Reason]
else if [Status] = "Quality-Reject" and [#"Quality-Rejection Reason"] = 0 then
else if [Status] = "Quality-Reject" then [#"Quality-Rejection Reason"]
[Remarks]
else 0
2
The check for Quality-Reject with a filled Quality-Rejection Reason must come before the check for an empty Quality-Rejection Reason :
if [Status] = "Rejected" then [Return Reason]
else if [Status] = "Quality-Reject" and [#"Quality-Rejection Reason"] <> "" then [#"Quality-Rejection Reason"]
else if [Status] = "Quality-Reject" and [#"Quality-Rejection Reason"] = "" then [Remarks]
else 0
SWITCH does not exist in PQ, you can create an fnSwitch(). (I recommend reading the article to avoid nested IF) :
The function takes 3 parameters:
1.string: the text value being operated on
2.switches: a nested list of values and replacements
3.default: the default value if there are no matches
let
fnSwitch = (string as text, switches as list, default as any) =>
let
// Get max index of replacement list
Max = List.Count(switches) - 1,
// Use List.Accumulate to loop through replacements
Switch = List.Accumulate(
switches,
default,
// if the string matches first index, then replace with second item in that nested list
// else keep the default
(state, current) => if string = current{0} then current{1} else state
)
in
Switch,
// Documentation
fnType = type function (string as text, switches as list, default as text) as any
meta [
Documentation.Name = "fnSwitch",
Documentation.LongDescription
= "This function takes a string and performs a SWITCH replacement using nested lists. Additionally, it provides a default value if there are no matches."
]
in
Value.ReplaceType(fnSwitch, fnType)
You will need to create a custom column and you set your mappings.