Power Query - Remove text between delimiters

Question

I want to remove any text between "( )" including the "( )". There are many difference instances where I can't simply find and replace. Small example:
ABC (1)
EFG (2)
XYZ (1, 2)
I wish to display
ABC
EFG
XYZ

Found this post, but the code for the function is no longer visible(at least on all the browsers I've tried). https://www.thebiccountant.com/2019/07/15/text-removebetweendelimiters-function-for-power-bi-and-power-query/

I copied the code from one of the comments and it seems to work fine, however when I invoke the function on the column I get all errors with the following: "Expression.Error: The specified index parameter is invalid. Details: List"

Does anyone have the code from the author? Or know what I'm doing wrong?

Here is the code from the new custom column after I run the function:

 Table.AddColumn(#"Changed Type1", "N", each Query1([#"NEC(s)"], "(", ")", 1, null))

Thanks

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2020-03-11T17:09:14

Here's a different solution that uses recursion.

(txt as text) =>
[ 
fnRemoveFirstTag = (DELIM as text)=>
    let
        OpeningTag = Text.PositionOf(DELIM,"("),
        ClosingTag = Text.PositionOf(DELIM,")"),
        Output = 
            if OpeningTag = -1 
            then DELIM 
            else Text.RemoveRange(DELIM,OpeningTag,ClosingTag-OpeningTag+1)
    in
        Output,
fnRemoveDELIM = (y as text)=>
    if fnRemoveFirstTag(y) = y
    then y 
    else @fnRemoveDELIM(fnRemoveFirstTag(y)),
Output = @fnRemoveDELIM(txt) 
][Output]

It works on your sample data, and should also work if there is more than one set of parentheses delimited substrings in your string.

Copied shamelessly and modified minimally from Power Query: remove all text between delimiters

Power Query - Remove text between delimiters

2 Answers