Remove duplicates in a text string with Power Query / Power BI

A few days ago I was checking social media to find interesting cases where Power Query could be a good solution. I found this scenario posted by Brian in a Facebook group here (url).

Imagine a scenario where you have a column with text values, but within each text string there are duplicates.

Take the previous image where from Column1 we want to:

1. Find the substring that gets repeated in the cell
2. Remove duplicates and keep only one instance of the substring that repeats itself

Effectively, we want what you see in the fxRemoveDuplicateString column.

In more clear terms, if we have a value such as:

this is a really long sentence this is a really long sentence this is a really long sentence this is a really long sentence

Then we want to pass that text string through a function that should output only the substring that repeats itself and starts from position 0:

this is a really long sentence

The function that does the magic

Note: The following section will be displaying a lot of advanced M language content.

First I’d like to share the custom function that I’ve created to solve this scenario and then I’ll go step by step as to what it does and why:

The function does exactly what we need. It tries to find the substring that gets repeated and, once it finds the best candidate, it removes the duplicates and keeps that single instance.

At first I tried to use the Column from example (url) option in Power Query, but this didn’t yield the result that I was expecting, so I had to create my own custom function.

Let’s step into the function to see how it works and why. You can download the sample file from the download button below:

Combinations

= List.Transform( {1.. Text.Length(Sample_Text)}, each Text.Start( Sample_Text,_))

The first step of the function is to get the total characters in the strings. That’s why I use the Text.Length function against the Sample_Text. Once I have that value, I create a list with the same length as the total characters in the text value.

For my example, I’m using the text this is a really long sentence this is a really long sentence this is a really long sentence as my sample text. This one has a lenght of 92 characters, so I create a list of 92 elements with the values from 1 through 92.

Then I go through each element in that list and apply the function Text.Start which brings me just the text string, but cut off for the number of characters that I pass in the second argument of the function which happen to be the element of that list that gets passed as the argument of the function.

Since we know that the start of the string will always start from the first character, the best idea that I came up with was to create this list of possible substrings and then test each one until I find the one that fits our logic. That’s why the first step just creates this list of potential or possible substrings that could become the output of this function.

Iterations and Total_Iterations

This is the step where the magic happens. Conceptually, what this step is doing is a while loop where I go element by element from the Combinations list to test which one would have the highest probability to be the one that’s being duplicated. I’m still trying to optimize this step as I only had around 40 minutes to come up and test this solution, but it’s looking good so far at the conceptual level.

The output of this step is simply a list with N elements which I later count at the Total_Iterations step.

Filtered_Possible_List

= List.Transform( Combinations, each Text.Split(Sample_Text, _){Total_Iterations})

The goal of the previous iteration was to achieve a list that started giving me errors and one element in the list that had a null, blank or empty value as you can see in the row 30 of the image below:

The reason why I’m trying to find the first iteration that starts giving me errors at the list level is because this tells me that those errors are not good candidates as they are not repeating themselves as much as the rest.

Table_From_Possible

I then create a new table from the Filtered_Possible_List and the Combinations lists using the Table.FromColumns function.

= Table.FromColumns({ Filtered_Possible_List, Combinations})

And this is when things start to make more sense visually.

Removed Errors

In this step I simply remove the errors from the field with the name Column1.

Filtered rows

Then I filter the table using the values from the field with the name Column1 to only keep the values that are either null, empty or with a blank space. These rows are effectively the best candidates as they were the ones that had an almost perfect or near perfect match for the substring.

Kept Last Rows and Custom3

In some cases I may end up with multiple candidates, so I added this step to always pick the last row from the table which, in theory, should always be the longest string or string with most characters:

And the last step of the function, called Custom3, simply navigates to the cell that contains the string thatt we need and that step is the output of the function:

Conclussion

This was a pretty interesting scenario and one that I can’t figure out how to solve without using some sort of while loop or some sort of recursive function. There’s a lot of room for improvement with this function and I hope to make those improvements at a later time.

Post your comments in the section below and let me know if you have any other interesting scenarios that I could tackle.