Replace values between square brackets in a Query

[Anonymous]

Hi, PowerBI newbie here but loving it!

In my Query I have a column where the values may have text in square brackets 

eg: This is some text [and this is other text]

I would like to remove the text between the square brackets.  This could be any text, so it needs to be dynamic

Any thoughts?

[OB]

Hi @ElenaN and @HotChilli,

How can this solution be adjusted to make multiple replacements in one text string? 

My example simplified to make it as close to the above solution as possible:  each time I have text in brackets (text in brackets will vary), I want to replace with space,

e.g. ABC [text1], EFG [text2], XYZ [text3] > ABC[ ], EFG[ ], XYZ [ ]

Thank you!

[ElenaN · Accepted solution]

Hello,

If you just want to replace the value in the brackets you can create a new column (Add Custom Column option in Power Query) and use this code:

=Text.Replace([Column1], Text.BeforeDelimiter(Text.AfterDelimiter([Column1], "["), "]"), "")

The code replaces whatever it is found between brackets with empty string. You can change that according to your needs.

Regards,

ElenaN

[SBSMN]

Thanks for this solution - I adapted it slightly and use Text.BetweenDelimiters but it will only find and remove the text for the first set of brackets in a cell, and not the rest. Do you have any ideas on how to remove more than one instance in a cell?

[Anonymous]

Position should be at the end, but maybe not always.  Splitting a column at a delimiter then deleting the one I dont need seems like overkill? 

M is new to me but I can code like half a dozen other languages to varying degrees of proficiency - its just a matter of learning the syntax and how to read the docs, right?

I feel like I'm super close with this, but how do I dynamically get the old value?  

= Table.ReplaceValue(#"Replaced Value1",Text.BetweenDelimiters(*** OLD VALUE ***,"[","]"),"",Replacer.ReplaceText,{"Email Name V2"})

https://docs.microsoft.com/en-us/powerquery-m/text-betweendelimiters

https://docs.microsoft.com/en-us/powerquery-m/table-replacevalue

[HotChilli]

You'll probably need an 'each' and a column reference in there.

I'm not sure why you think the split column idea is overkill.  It takes about 2 seconds to do it in the Power Query editor.

[HotChilli]

Depending on your sample data, for example if the extra clause is always the end of a string and never has anything after it, you could split the column in Power Query editor on the first occurrence of '[' and just use the first column.

Other forms of splitting and joining columns may work for you.

The 'true' algorithm for all cases would be : Find the position of '[' , Find the position of a subsequent ']' and remove the string between the two but i suspect that might be 'M' language that is further down your powerbi journey.