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:
Convert to proper case with exceptions
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”.
Solution
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:
Split text of each row into a list
Then I proper case each element in the list (including the ones that should actually be excluded!):
Proper case every element within the list
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:
Reorder columns, so that “From” comes first
Then I transform this to a list of list so that it can be used by the replacements function:
Transform to list of lists
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):
Replacement function translates proper cased word back to the original values
The last step is to stitch back the list into a text-string:
Stitch the list back together 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 😉
Pingback: Transforming To Proper Case In Power BI, With Exceptions – Curated SQL
Thanks for the topic and solution:
My suggestion is below:
= Table.AddColumn(Source, “Result”, each Text.Combine(List.ReplaceMatchingItems(Text.Split(Text.Proper([Column1]),” “),List.Transform(KeepTable,each {Text.Proper(),})),” “))