Recently I picked up an interesting request to transform text with exceptions: Transform the words in a column to proper case, but keep certain keywords like in a defined list.
Problem: Transform text with exeptions
Say you have a list with specific terms that shouldn’t be proper cased like so:
And you want to proper case the following column:
So I proper case each word that is not contained in the “KeepTable”, identify the elements in the “KeepTable” in a case insensitive way and transform them into the syntax that’s specified in the “KeepTable”.
The overall strategy is to convert everything to proper case first and then use a translation table to convert the keywords from the table back to their desired values. The following steps show how to do it:
First I split the column with the values to be proper cased into nested lists:
Then I proper case each element in the list (including the ones that should actually be excluded!):
Now I just have to translate the proper cased keywords from my “KeepTable” to their original values. Therefore I need a translation table like from my multiple replacements solution and use the technique from this blogpost to achieve the desired result:
Starting from the “KeepTable” I add the proper cased “From”-column like so:
Then reorder the columns, so that the “From”-column comes first:
Then I transform this to a list of list so that it can be used by the replacements function:
This list of lists can now be used in the translation operation. Therefore I reference the step where I have proper cased the original column (green) and perform the translation (yellow):
The last step is to stitch back the list into a text-string:
Please check out this file to follow the solution along: CapitalizeWithExeptionsUpload.zip
You will see that this a solution that is mostly achieved by using the UI and adding columns to the table.
Enjoy and stay queryious 😉