CODEWITHSUNDEEP
excelpowerbipowerquery
Connor
Connor

Reputation: 21

Power Query - Remove text between delimiters

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

Upvotes: 0

Views: 3841

Answers (3)

chrimaho
chrimaho

Reputation: 684

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

Upvotes: 0

horseyride
horseyride

Reputation: 21298

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}})

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

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.

enter image description here

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

Upvotes: 1

Related Questions