Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
abellanc
Regular Visitor

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

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

PartNoFailureMode
ABC123Shorted IC (ACT1234)
ASC456Open OP_AMP (ACT5678)
ABC123Wrong_Output IC (ACT1234)
ABC789Stuck Relay (SWI1234)

 

Output

PartNoFailureMode
ABC123Defective IC (ACT1234)
ASC456Defective OP_AMP (ACT5678)
ABC123Defective IC (ACT1234)
ABC789Stuck Relay (SWI1234)
4 ACCEPTED SOLUTIONS
AlienSx
Super User
Super User

    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"}
    )

View solution in original post

shafiz_p
Super User
Super User

@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!!

View solution in original post

ThxAlot
Super User
Super User

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"})

ThxAlot_0-1720360541287.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



View solution in original post

dufoq3
Super User
Super User

Hi @abellanc, another (similar) solution here:

 

Result

dufoq3_0-1720454739673.png

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

7 REPLIES 7
Khushidesai0109
Resolver I
Resolver I

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
Regular Visitor

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

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
Super User
Super User

Hi @abellanc, another (similar) solution here:

 

Result

dufoq3_0-1720454739673.png

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

ThxAlot
Super User
Super User

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"})

ThxAlot_0-1720360541287.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



shafiz_p
Super User
Super User

@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
Super User
Super User

    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"}
    )

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors