Forum Discussion
Replace text within a cell based on another field
I'm having trouble finding a solution to this issue, as there's lots of guides to similar, but different problems. Any help is appreciated.
I have a table with university courses. I would like to replace text within each field based on an if statement on another field.
The other guides that I have found are based around replacing the whole cell contents.
| CourseTitle | CourseLevel |
| Master in Science in Mathematics | Undergraduate |
| Master in Science in Computer Science | Undergraduate |
| Master in Science in Computer Science | Postgraduate |
| Master in Science in Data Analysis | Postgraduate |
I want to abbreviate the long course names. I know how to do a replace:
= Table.ReplaceValue(#"Filtered Rows","Master in Science in","MSc",Replacer.ReplaceText,{"CourseTitle"})
This results in 'MSc Mathematics' , etc.
But I would like it that if CourseLevel equals "Undergraduate", replace the text 'Master in Science in' with 'MSci' in CourseTitle.
Otherwise change 'Master in Science in' with 'MSc', (or if it's easier, simply keep the text in the column CourseTitle.)
IT is probably easier if you use the UI to just add a column, and transform as you like (you can use column from example). If you like writing the code directly it would be:
Table.TransformRows(#"Filtered Rows", each _ & [CourseTitle = Text.Replace([CourseTitle], "Master in Science in", if [CourseLevel] = "Undergraduate" then "Msci" else "Msc")])
3 Replies
IT is probably easier if you use the UI to just add a column, and transform as you like (you can use column from example). If you like writing the code directly it would be:
Table.TransformRows(#"Filtered Rows", each _ & [CourseTitle = Text.Replace([CourseTitle], "Master in Science in", if [CourseLevel] = "Undergraduate" then "Msci" else "Msc")])Hi artemus , thanks for your reply.
This transform the table into a list with a string of Records.As for adding a new column and changing that; I have to end up with the query outputting a column called 'CourseTitle'. it seems inefficient to create a new column with the changes (CourseTitle2), then delete the original 'CourseTitle' and rename the new one to 'CourseTitle'.
Oh yea, you can just wrap Table.FromRecords(previousStep) if you want to turn it back into a table.
