Power BI if statement / replace

[Anonymous]

How do I replace whole cells in a column if few of those cells contain a specific text along with other words. As you can see from the picture, I am trying to replace all the cells that contain the word DUCTWORK (highlighted) with HVAC, and would like to replace all the content in that cell with it (HVAC). i.e, If cell contains the word ductwork, replace whole cell with hvac.

Thank you

[v-angzheng-msft]

Hi, @Anonymous 

May I ask if your problem has been solved? Is the above post helpful to you?

If  it does, could you please mark the post which help as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.

Based on my research, the formula provided by @Resty  will work for you
Result:

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jennratten]

There are a couple of enhancements that may be helpful to include in your script - please see below.

If you start with these values (note, row 2 includes DUCTWORK but it is lowercase):

Option 1 - replacements are case sensitive and the resulting column is not of type text:

Table.TransformColumns(#"Changed Type", {{"Sheet Name - Copy", each if Text.Contains(_, "DUCTWORK") then "HVAC" else _}})

Replaces instances in which the keyphrase with matching case is found. The result also returns type any instead of type text, creating an additional step for you to change back to text.

Option 2 - make the replacements perform without case sensitivity and the resulting column is of type text.

Returning the column as text is done by specifying "type text" as the third argument inside of the transformation function.

Performing the replacements with case insensitivity is done by specifying "Comparer.OrdinalIgnoreCase" as the third argument inside of the Text.Contains function.

Table.TransformColumns(#"Changed Type", {{"Sheet Name - Copy", each if Text.Contains(_, "DUCTWORK", Comparer.OrdinalIgnoreCase) then "HVAC" else _, type text}})

[Resty · Accepted solution]

Hi,

please insert next step that will use this:

= Table.TransformColumns(NameOfPreviousStep, {{"Sheet Name - Copy", each if Text.Contains(_, "DUCTWORK") then "HVAC" else _ }})

just make sure that you will replace NameOfPreviousStep with your previous steps name.

Hope it helps!