Remove strings within parentheses from Power Query Editor

[ELW]

Is there a way to remove various strings from within parentheses from the query editor in PowerBI?  There could be 0, 1, or multiple instances of text within parenthesis I need to remove from my strings.  I need to do it within the query.  I've tried using ReplaceValue with an asterisk, but that doesn't seem like it works this way.  

Here's a gibberish example: 

BEFORE	AFTER
Lorem ipsum (ABC) dolor sit amet	Lorem ipsum dolor sit amet
consectetur adipiscing elit (DE)	consectetur adipiscing elit
sed do (FGHI) eiusmod tempor incididunt	sed do eiusmod tempor incididunt
ut labore et dolore (JKLM) magna aliqua (NOP)	ut labore et dolore magna aliqua
Ut enim ad minim veniam	Ut enim ad minim veniam

Thanks!

[Anonymous]

i have multiple instances of parenthesis in the column. I tried solution provided here it worked but only for first instance of a cell. Refer the attached screenshot. Any help is appreciated.

[parry2k]

@ELW add new custom column in power query with following expression

Text.Replace(Text.Replace([BEFORE],"(",""), ")", "" )

[ELW]

Thanks @parry2k!  I believe that just replaces the parentheses themselves while leaving the text inside them.  Is there a way to modify that formula to remove the text from inside the parentheses as well? 

[v-yuta-msft · Accepted solution]

@ELW ,

An simple way than power query is to write a python script to replace (...) using non-greedy matching.

# 'dataset' holds the input data for this script

import pandas as pd

dataset["AFTER"] = dataset.replace(to_replace=r'\(.*?\)', value='', regex=True)

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.