Change value to OP_AMP if cell contains specific text

[abellanc]

If FailMode column contains "ACT1234G", "ACT5678G", I want to change the word IC or Transistor (depends on the word) to OP_AMP. Retain others if it doesn't contain the mentioned criteria.

Original:

FailMode
Shorted IC (ACT1234G)
Open Transistor (ACT5678G)
Defective Relay (SWI1234G)

Output:

FailMode
Shorted OP_AMP (ACT1234G)
Open OP_AMP (ACT5678G)
Defective Relay (SWI1234G)

[abellanc]

nice, thanks!!

[Greg_Deckler · Accepted solution]

@abellanc Try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs7ILypJTVHwdFbQcHQOMTQyNnHXVIrViVbyL0jNUwgpSswrziwuyS8CS5uamVtApV1S01KTSzLLUhWCUnMSKxU0gsM9YbpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FailMode = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FailMode", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([FailMode], "ACT1234G") or Text.Contains([FailMode], "ACT5678G") then Text.Replace(Text.Replace( [FailMode], " IC ", " OMP_AMP "), " Transistor ", " OMP_AMP ") else [FailMode]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"FailMode"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "FailMode"}})
in
    #"Renamed Columns"