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
Enter your email address below to download the sample workbook.
Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.
The way I'm going to approach the problem is
- Split the text string at every )
- Extract the text from the beginning of each sub-string, up to the (
- 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.
Tips for Using The Power Query Advanced Editor
Doris Kong
Thank you for the video and sharing a better way to replace delimiters easily which I previously used split/replace to achieve that. Would appreciate if you can show how to split text with delimiters in a row to multiple rows. I have received data file that combine all texts in a row separated by delimiters like “,” “.” or “/”.
Philip Treacy
Hi Doris,
Please start a topic on our forum and attach your file so I can see what data you are working with.
Regards
Phil
David Lavery
I’m trying, and struggling, to expand/adapt this to work in the case of the (..) string NOT appearing in a source row. Any pointers? Thanks
Philip Treacy
Hi David,
I’m not clear what you are trying to do. Can you provide an example with some sample data?
Regards
Phil
David Lavery
Basically, what if one/some field values do NOT contain the delimiter/s (get an error otherwise)
Philip Treacy
Hi David,
You can modify the step that looks for the delimiter. If the delimiter is not found, just return the original data
= Table.AddColumn(#"Added Custom", "Custom.1", each List.Transform([Custom], each try Text.Start(_ , Text.PositionOf( _, "(")) otherwise _ ))try otherwise catches the error (there’s no delimiter) and replaces the error with the current item in the row from the [Data] column, specified by the _
Regards
Phil
Maggie Wang
I’ve learned tons of tricks with this video, thank you very much Phil. Just a quick query, when running the keep delimiters version of the function, the () are indeed appearing against the first three items but not the last (4th), why is this happening? Thank you.
Philip Treacy
Thanks Maggie.
Good spot on that, I missed it, doh.
I’ve modified the L3 step in the function so the last set of delimiters are returned if desired. I’ve updated the Excel workbook that can be downloaded but this is the new section of code.
Text.Combine(List.Combine({Text.ToList(Text.Combine( L2 , Text.Combine({LeftDelim, RightDelim} ))),{LeftDelim}, {RightDelim}}))Regards
Phil
Tim Hoogenboom
That’s quite a whopper. I could use further explanation. Thanks.
L3 = if KeepDelimiters then
Text.Combine( L2 ,
Text.Combine(
List.Combine(
{
Text.ToList( Text.Combine( L2 , Text.Combine( { LeftDelim, RightDelim } ) ) ),
{LeftDelim}, {RightDelim}
}
)
)
)
else Text.Combine( L2 )
Catalin Bombea
Hi Tim,
Your L3 step looks different than the one provided in the article, make sure you use the same code. If you are trying to change the functionality, please describe what you are trying to achieve.
Cheers,
Catalin