Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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!
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
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |