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 ūüėČ

Comment (1) Write a comment

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

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz