Change value to Defective if cell contains specific text EXCEPT for a specific PartNo

[abellanc]

Hi, I would like to change the text "Shorted", "Open", "Wrong_Output" on Failure Mode to "Defective" EXCEPT for PartNo ABC789 without adding a new column. 

I initially used below formula but it has an "Expression.Error: We cannot apply field access to the type Text" error. Both PartNo and FailureMode type are text.

#"ReplaceFailureMode" = Table.TransformColumns(
Source,
{{
"FailureMode",
each if [PartNo] <> "ABC789" and List.Contains({"Shorted", "Stuck", "Open", "Wrong_Output"}, _)
then "Defective"
else _
}}
)

Input

PartNo	FailureMode
ABC123	Shorted IC (ACT1234)
ASC456	Open OP_AMP (ACT5678)
ABC123	Wrong_Output IC (ACT1234)
ABC789	Stuck Relay (SWI1234)

Output

PartNo	FailureMode
ABC123	Defective IC (ACT1234)
ASC456	Defective OP_AMP (ACT5678)
ABC123	Defective IC (ACT1234)
ABC789	Stuck Relay (SWI1234)

[AlienSx · Accepted solution]

    Table.ReplaceValue(
        Input, 
        (x) => x[PartNo] <> "ABC789", 
        (x) => List.PositionOf(
            {"Shorted", "Stuck", "Open", "Wrong_Output"},
            x[FailureMode], 
            Occurrence.First, 
            (x, y) => Text.Contains(y, x)
        ),
        (v, o, n) => 
            if o and n <> -1  
            then Text.Replace(
                v, 
                {"Shorted", "Stuck", "Open", "Wrong_Output"}{n}, 
                "Defective"
            )
            else v,
        {"FailureMode"}
    )

[shafiz_p · Accepted solution]

@abellanc  Please use the following code :

ReplaceValue = Table.ReplaceValue(
#"Changed Type",
each [FailureMode],
each if Text.Contains([FailureMode], "Shorted") and [PartNo] <> "ABC789" then Text.Replace([FailureMode], "Shorted", "Defective")
else if Text.Contains([FailureMode], "Stuck") and [PartNo] <> "ABC789" then Text.Replace([FailureMode], "Stuck", "Defective")
else if Text.Contains([FailureMode], "Open") and [PartNo] <> "ABC789" then Text.Replace([FailureMode], "Open", "Defective")
else if Text.Contains([FailureMode], "Wrong_Output") and [PartNo] <> "ABC789" then Text.Replace([FailureMode], "Wrong_Output", "Defective")
else [FailureMode],
Replacer.ReplaceText,
{"FailureMode"}
)

Hope this Helps!!

If this solved you problem, please mark it as a solution!!

[ThxAlot · Accepted solution]

Easy enough,

= Table.ReplaceValue(Source, each [PartNo], null, (x,y,z) => if y<>"ABC789" then List.Accumulate({"Shorted", "Open", "Wrong_Output"}, x, (s,c) => Text.Replace(s, c, "Defective")) else x, {"FailureMode"})

[Khushidesai0109]

Hiiii @abellanc 

Try this below 

let
Source = YourSourceTable, // Replace this with your actual source table reference
ReplaceFailureMode = Table.TransformColumns(
Source,
{{"FailureMode",
each if [PartNo] <> "ABC789" and List.Contains({"Shorted", "Open", "Wrong_Output"}, Text.Split(_, " "){0})
then "Defective " & Text.AfterDelimiter(_, " ")
else _
}}
)
in
ReplaceFailureMode

If this solved you problem, please mark it as a solution!! also kudos are appreciated 

[abellanc]

Hi Guys, I will check this weekend, work mode for the weekday. 🙂 Thanks!

[v-jingzhan-msft]

Hi @abellanc 

Have you resolved this issue? If any of the answers provided were helpful, please consider accepting them as a solution. If you have found other solutions, we would greatly appreciate it if you could share them with us. Thank you!

Best Regards,
Jing

[dufoq3 · Accepted solution]

Hi @abellanc, another (similar) solution here:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyVtJRCs7ILypJTVHwdFbQcHQOAQqaaCrF6gBVBDubmJoBVfgXpOYp+AfEO/oGgJWYmplbQJXADAkvys9Lj/cvLSkoLcFikpOzuYUlyK6S0uRshaDUnMRKBY3gcE+oklgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PartNo = _t, FailureMode = _t]),
    ReplaceFailureMode = [ a = List.Buffer({"Shorted", "Stuck", "Open", "Wrong_Output"}),
    b = Table.ReplaceValue(Source,
                each [PartNo] <> "ABC789" and List.Contains(a, [FailureMode], (x,y)=> Text.Contains(y, x)),
                each Text.Split([FailureMode], " "),
                (x,y,z)=> if y then Text.Combine(List.ReplaceMatchingItems(z, List.Transform(a, (w)=> {w, "Defective"})), " ") else x,
                {"FailureMode"} )
  ][b]
in
    ReplaceFailureMode

[ThxAlot · Accepted solution]

Easy enough,

= Table.ReplaceValue(Source, each [PartNo], null, (x,y,z) => if y<>"ABC789" then List.Accumulate({"Shorted", "Open", "Wrong_Output"}, x, (s,c) => Text.Replace(s, c, "Defective")) else x, {"FailureMode"})

[shafiz_p · Accepted solution]

@abellanc  Please use the following code :

ReplaceValue = Table.ReplaceValue(
#"Changed Type",
each [FailureMode],
each if Text.Contains([FailureMode], "Shorted") and [PartNo] <> "ABC789" then Text.Replace([FailureMode], "Shorted", "Defective")
else if Text.Contains([FailureMode], "Stuck") and [PartNo] <> "ABC789" then Text.Replace([FailureMode], "Stuck", "Defective")
else if Text.Contains([FailureMode], "Open") and [PartNo] <> "ABC789" then Text.Replace([FailureMode], "Open", "Defective")
else if Text.Contains([FailureMode], "Wrong_Output") and [PartNo] <> "ABC789" then Text.Replace([FailureMode], "Wrong_Output", "Defective")
else [FailureMode],
Replacer.ReplaceText,
{"FailureMode"}
)

Hope this Helps!!

If this solved you problem, please mark it as a solution!!

[AlienSx · Accepted solution]

    Table.ReplaceValue(
        Input, 
        (x) => x[PartNo] <> "ABC789", 
        (x) => List.PositionOf(
            {"Shorted", "Stuck", "Open", "Wrong_Output"},
            x[FailureMode], 
            Occurrence.First, 
            (x, y) => Text.Contains(y, x)
        ),
        (v, o, n) => 
            if o and n <> -1  
            then Text.Replace(
                v, 
                {"Shorted", "Stuck", "Open", "Wrong_Output"}{n}, 
                "Defective"
            )
            else v,
        {"FailureMode"}
    )