Forum Discussion

TM_Visual's avatar
TM_Visual
Icon for Advocate III rankAdvocate III
6 years ago
Solved

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 re...
Need Help
  • artemus's avatar
    artemus
    6 years ago

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

     

artemus's avatar
artemus
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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

 

  • TM_Visual's avatar
    TM_Visual
    Icon for Advocate III rankAdvocate III
    6 years ago

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

    • artemus's avatar
      artemus
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      Oh yea, you can just wrap Table.FromRecords(previousStep) if you want to turn it back into a table.