Transform text with exceptions in Power BI and Power Query

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 😉

Comments (2) Write a comment

  1. Pingback: Transforming To Proper Case In Power BI, With Exceptions – Curated SQL

  2. 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(),})),” “))

    Reply

Leave a Reply