Remove Text Between Delimiters – Power Query

This is my source data and I want to remove the percentages

Specifically I want to remove all occurrences of (00%) so my data ends up like this

There's no native function in Power Query to do this so I'll have to write my own code.

Watch the Video

 

Download Sample Excel Workbook

The way I'm going to approach the problem is

1. Split the text string at every )
2. Extract the text from the beginning of each sub-string, up to the (
3. Recombine the sub-strings

With my source data loaded into Power Query, let's get to it.

Split Text By the Right-Most Delimiter

I'm going to call the ) my right-most delimiter. I want to split the text string at every occurrence of a ) so each ) will form the end, or the right hand side, of each substring.

With the query selected, add a Custom Column, using Text.Split to split the text string at every )

Leaving that code as it is will result in an empty element in the last position of the list that Text.Split creates, so I wrap the whole thing in List.RemoveMatchingItems to remove empty strings i.e. ""

The new column contains Lists as shown here

Extract Text Before the Left Delimiter

Now I need to get the text before the (, I can do this with a Text.Start which extracts a string from each string in the Lists in the Custom column.

For example, Text.Start takes this Brisbane/4000 (19% and turns it into this Brisbane/4000

List.Transform then takes these new strings from Text.Start and puts them into the Lists in the Custom column, replacing the values already there. This is the result

Recombine the Sub-Strings to Get the Result

The last part is to use Text.Combine to recombine the sub-strings in the Lists to get our text string back, minus the bits we don't want.

Create a Function

That's great, I have code I can use to remove text between delimiters, but every time I want to use it, I'll have to rewrite it or modify it.

If I write a function I can reuse the function any time I like and not have to rewrite or modify any code.

To begin, I duplicate the query I just finished and rename it fx_Remove_Text_Between_Delimiters

Open the Advanced Editor and gaze in wonder at the code.

The first thing I'll do to transform this code into a function is change the Source step to a function declaration and specify that the function takes 3 arguments called TextString, LeftDelim and RightDelim.

Then I'll rename the #"Added Custom" steps, delete the #"Removed Columns" step, change the in statement so that step L3 is returned, and not forget to remove the comma from the end of the last step before the in.

Then I can delete the Table.AddColumn functions from each step. I'm not adding columns to a function here so these are not needed.

Now I can insert the function arguments into the steps

and insert the step names in the correct places

The last thing to do is insert a let keyword after the Source step, and add a new in Source line right at the end to say the function is returning the value of the Source step (which is the result of the L1, L2 and L3 steps).

Add some indentation to make the code layout more readable and the function is finished.

Call the Function

After closing the Advanced Editor I can add a new column in my main query, and I'll be invoking a custom function.

The new column will be called Cleaned Data, the function query is the function I just wrote, TextString is the Data column, LeftDelim is ( and RightDelim is )

Giving this new column

What If You Want to Keep the Delimiters?

This code removes the delimiters too. If you want to keep the delimiters then you need to do two things.

First, pass in an argument that tells the function if you want to keep the delimiters, or not. Second, modify the function's last line to check this argument and combine the text with or without the delimiters.

Here's what that function looks like

Calling the function looks like this, by setting KeepDelimiters to TRUE I'm telling the function to keep the delimiters in the string it returns.