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'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.)
Solved! Go to Solution.
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")])
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.
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.