cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate II
Advocate II

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.

 

CourseTitleCourseLevel
Master in Science in MathematicsUndergraduate
Master in Science in Computer ScienceUndergraduate
Master in Science in Computer SciencePostgraduate
Master in Science in Data AnalysisPostgraduate

 

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

 

1 ACCEPTED SOLUTION
Microsoft
Microsoft

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

 

View solution in original post

3 REPLIES 3
Microsoft
Microsoft

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

 

View solution in original post

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.

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors