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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
abellanc
Regular Visitor

Change value to OP_AMP if cell contains specific text

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)

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
abellanc
Regular Visitor

nice, thanks!!

Greg_Deckler
Super User
Super User

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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors