Replace values with text contains

Hi massotebernoull 

Maybe you have found a solution, but since there is not an answer yet here, I'd like to share my ideas. 😉

You can add a custom column for SCHOOL column and remove the original one. I add [SCHOOL]="" in the if statement. You need to trim SCHOOL column (go to Transform > Format > Trim) before adding this custom column in case there is any invisible space in it.

= Table.AddColumn(#"Trimmed Text", "Custom", each if [SCHOOL]="" and Text.Contains([USER],"@school1.com") then "school1" else [SCHOOL])

If you want to replace values in the original column, you can use below code to add a step.

= Table.ReplaceValue(#"Trimmed Text", each [SCHOOL], each if [SCHOOL]="" and Text.Contains([USER],"school1.com") then "school1" else [SCHOOL], Replacer.ReplaceValue, {"SCHOOL"})

If you have other solutions, can you share them here to help more people who may have similar questions?

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.