Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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.
= Table.ReplaceValue(#"Filtered Rows", each [USER],"school1",(x,y,z)=>if Text.Contains(y, "school1") then z else x, {"SCHOOL"})
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 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?
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
To learn more about Power BI, follow me on Twitter or subscribe 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 !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.