Forum Discussion
Replace values between square brackets in a Query
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?
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
6 Replies
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
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?
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.
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
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.
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!
