Dan Bliss
New member
- Joined
- Dec 7, 2016
- Messages
- 45
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- Office 365
I have a column of Names [Name] with some near matches, such as "Cessna Aircraft Co." & "Cessna AircraftCo." I'm looking to remove the Co.'s and Inc.'s and such - at the end of the string value. Brute force UI method using Table.ReplaceValue(#"Replaced Value","Inc.","",Replacer.ReplaceText,{"Name"}) only works on a single string at a time.
I want to develop a robust solution replacing all offending suffixes: I created a separate table called [NamePartsToRemove] using Table.SplitColumn on [Names], extracting the right most string before space delimiter. Eliminated dups and filtered the table so it includes only strings like "Inc" & "Co." Now I need to match each row in [Name] against all entries in [NamePartsToRemove] and replace any match with an empty string "". I can't find a PowerQuery search function that will help with this neat trick. If need be, [NamepartsToRemove] can be quickly changed into a list. But where is the necessary SEARCH function that searches elements on a list or values on a table?
I want to develop a robust solution replacing all offending suffixes: I created a separate table called [NamePartsToRemove] using Table.SplitColumn on [Names], extracting the right most string before space delimiter. Eliminated dups and filtered the table so it includes only strings like "Inc" & "Co." Now I need to match each row in [Name] against all entries in [NamePartsToRemove] and replace any match with an empty string "". I can't find a PowerQuery search function that will help with this neat trick. If need be, [NamepartsToRemove] can be quickly changed into a list. But where is the necessary SEARCH function that searches elements on a list or values on a table?
Last edited: