Replace values with text contains

[massotebernoull]

I Have this table

USER	SCHOOL
posada@school1.com
ling@school1.com
carla@school14.com	school4
mark@school1.com	school1
Alex@school1.com	school1
alexandra@school1.com
justen@school2.com	school2
marcel@school3.com	school3
anna@hotmail.com	school5

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?

[v-jingzhang]

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.

[wdx223_Daniel]

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

[Anonymous]

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

[massotebernoull]

I Have this table

USER	SCHOOL
posada@school1.com
ling@school1.com
carla@school14.com	school4
mark@school1.com	school1
Alex@school1.com	school1
alexandra@school1.com
justen@school2.com	school2
marcel@school3.com	school3
anna@hotmail.com	school5

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?

[mahoneypat]

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

[massotebernoull]

Sorry for not specifying it properly.

I need that only null lines that have "@school1.com" in column USER change to "school1" in column SCHOOL.

Other null values from other schools must continue as they are.

Thanks for replying @mahoneypat !