Forum Discussion
Solved
How to Remove Multiple Substrings from a String of Text in Power Query
Hi Team,
I need to remove specific strings from the end of a string; however, not every field contains these values, so I cannot simply remove a certain number of characters from the end of the string. Here is some sample data:
Job Title
| Asset Management Mgmt Level 1 |
| Asset Management Mgmt Level 2 |
| Asset Management Mgmt Level 3 |
| Asset Management Mgmt Level 4 |
| Bid Mgmt Level 2 |
| Bid Mgmt Level 3 |
| Bid Mgmt Level 4 |
| Bid Specialist |
| Bid Specialist Level 1 |
| Bid Specialist Level 2 |
| Bid Specialist Level 3 |
| Bid Specialist Level 4 |
| Bid Specialist Level 5 |
| Billing Administrator Level 2 |
| Billing Administrator Level 3 |
| Billing Administrator Level 4 |
| Billing Administrator Level 5 |
I need to remove all instances of the following (or replace them with null):
" Level 1"
" Level 2"
" Level 3"
" Level 4"
" Level 5"
" Level 6"
I know I can do this one by one with six steps like the below, but is there a way to do this in one step?
= Table.ReplaceValue(#"Changed Type2", " Level 1", "", Replacer.ReplaceText,{"Job Title"})
Thanks in advance for any assistance!
You can use
= Table.TransformColumns(#"Changed Type", {{"Job Title", each Text.BeforeDelimiter(_, " Level "), type text}})This assumes that the word 'Level' will not appear in a job title you want to keep.
