Formula to replace text if condition from another column

[meileetan]

I would like to replace text in "Client" column with value of "Company" if the value in "Project" column = Capacity. I can't figure out what is wrong with my formula below - can someone help review? I tried 2 versions:

Version 1
= Table.ReplaceValue(#"Replaced Project TO to Cap",
each [Client],
each if Text.Contains([Project],"Capacity") then "Company" else [Client],
Replacer.ReplaceText,{"Client"})

Version 2
= Table.ReplaceValue(#"Replaced Project TO to Cap",
each [Client],
each if [Project] = "Capacity" then "Company" else [Client],
Replacer.ReplaceText,{"Client"})


The original cells were blank and remained so after the formula was applied.

 

[JGordon11]

Table.ReplaceValue doesn't work that way. It looks in all the cells of the table for OldValue and replaces with NewValue. So the way you have written it the function is looking in all cells of the table for a function that looks like (_)=>_[Client], and replace if found with another function. Since none of your cells have that function in them, it finds nothing to replace, and it doesn't throw an error because finding nothing is an acceptable result.

There may be a better way but I would

Add a column: Table.AddColumn(#"Replaced Project TO to Cap", "ClientR1", each if [Project] = "Capacity" then "Company" else [Client])
Delete the old Client Column: Table.RemoveColumns(...)
Rename ClientR1 back to Client: Table.RenameColumns(...)
Reorder back to the original order: Table.ReorderColumns(PreviousStepName, Table.ColumnNames(#"Replaced Project TO to Cap"))

 

[meileetan]

I don't know the logic behind the formula but I know it works because I have done it for other columns in another workbook. Anyway, version 2 mysteriously worked today. I have no idea why! ?‍?

 

[JGordon11]

You're right - Table.ReplaceValue has a lot of functionality I didn't realize. Thanks for pointing this out.