Forum Discussion

ELW's avatar
ELW
Advocate II
7 years ago
Solved

Remove strings within parentheses from Power Query Editor

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: 

 

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

 

Thanks!

Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    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.

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    ELW add new custom column in power query with following expression

     

    Text.Replace(Text.Replace([BEFORE],"(",""), ")", "" )
    • ELW's avatar
      ELW
      Advocate II
      7 years ago

      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's avatar
        v-yuta-msft
        Community Support
        7 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.