Replace values with text contains

= Table.ReplaceValue(#"Filtered Rows", each [USER],"school1",(x,y,z)=>if Text.Contains(y, "school1") then z else x, {"SCHOOL"})

Your code worked for me.  The only change I made was to use "school1.com" instead of just "school1" in the Text.Contains, as school14 contains school1 for example.

Pat

I Have this table

and I have to replace some null values with "school1". The problems is:

- the problem is always with school1;

- I need do take the email domain as a key to identifying the missing values from school1; 

- If I have other null values in collumn SCHOOL different of "school1" must continue null, but all lines that have @school1 in USER must have school1 in SCHOOL.

I tried this formula, but it fail

= Table.ReplaceValue(#"Filtered Rows", each [SCHOOL], each if Text.Contains([USER], "school1") then "school1" else [SCHOOL], Replacer.ReplaceText, {"SCHOOL"})

It didn't change at all the table. What am I doing wrong?

Might be easiest to add a column:

= Table.AddColumn(TableName, "Corrected", each if Text.Contains([User], "@school1.com") then "school1" else [User], type text)

--Nate

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.