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

Answer 1

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

Answer 2

Is there text to the right of the )?

If not, just split column on custom delimiter ( leftmost, then remove the 2nd column

= Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"})

OR transform the column to remove anything after the initial (

= Table.TransformColumns(Source,{{"Column1", each Text.Start(_,Text.PositionOf(_,"(")), type text}})

If text to the right of the ), try

= Table.TransformColumns(Source,{{"Column1", each Text.Start(,Text.PositionOf(,"("))&Text.End(,Text.Length()-Text.PositionOf(_,")")-1), type text}})

Answer 3

There is an even simpler solution.

You can create a new function called fun_ReplaceTextBetweenDelimiters, and in it add this code

let
    fun_ReplaceTextBetweenDelimiters = (Text as text, StartDelimiter as text, EndDelimiter as text, optional ReplaceDelimiters as nullable logical, optional NewText as nullable text, optional TrimResult as nullable logical, optional FixDoubleSpaces as nullable logical) as text => 
        let
            // Add Default Parameters
            Default_ReplaceDelimiters = if ReplaceDelimiters is null then true else ReplaceDelimiters,
            Default_NewText = if NewText is null then "" else NewText,
            Default_TrimResult = if TrimResult is null then true else TrimResult,
            Default_FixDoubleSpaces = if FixDoubleSpaces is null then true else FixDoubleSpaces,
            //Do work
            TextBetweenDelimiters = Text.BetweenDelimiters(Text, StartDelimiter, EndDelimiter),
            TextToReplace = if Default_ReplaceDelimiters then Text.Combine({StartDelimiter,TextBetweenDelimiters,EndDelimiter}) else TextBetweenDelimiters,
            ReplacedText = Text.Replace(Text, TextToReplace, Default_NewText),
            //Clean Result
            TrimmedText = if Default_TrimResult then Text.Trim(ReplacedText) else ReplacedText,
            FixedSpaces = if Default_FixDoubleSpaces then Text.Replace(TrimmedText, "  ", " ") else TrimmedText
        in
            FixedSpaces
in
    fun_ReplaceTextBetweenDelimiters

Then, we can test it like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtAw1FTSAbGUYnWilVzd3BU0jEAiQBZYJCIyCqhGRwEsCOQoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TestData = _t, TargetData = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"TestData", type text}, {"TargetData", type text}}),
    RunFunction = Table.AddColumn(ChangeType, "NewText", each fun_ReplaceTextBetweenDelimiters([TestData], "(", ")", true), type text),
    TestResult = Table.AddColumn(RunFunction, "Test", each [TargetData]=[NewText], type logical)
in
    TestResult

Input:

TestData	TargetData
ABC (1)	ABC
EFG (2)	EFG
XYZ (1, 2)	XYZ

Output:

TestData	TargetData	NewText	Test
ABC (1)	ABC	ABC	TRUE
EFG (2)	EFG	EFG	TRUE
XYZ (1, 2)	XYZ	XYZ	TRUE