cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
massotebernoull
Frequent Visitor

Replace values with text contains

I Have this table

 

USERSCHOOL
posada@school1.com 
ling@school1.com 
carla@school14.comschool4
mark@school1.comschool1
Alex@school1.comschool1
alexandra@school1.com 
justen@school2.comschool2
marcel@school3.comschool3
anna@hotmail.comschool5

 

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?

6 REPLIES 6
v-jingzhang
Community Support
Community Support

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])

21110303.jpg

 

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"})

21110304.jpg

 

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
Super User
Super User

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

watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
massotebernoull
Frequent Visitor

I Have this table

 

USERSCHOOL
posada@school1.com 
ling@school1.com 
carla@school14.comschool4
mark@school1.comschool1
Alex@school1.comschool1
alexandra@school1.com 
justen@school2.comschool2
marcel@school3.comschool3
anna@hotmail.comschool5

 

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
Super User
Super User

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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 !

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors