Remove Text Between - Power BI

[kenchan]

I am working on removing some text with pattern below "[cig ...]"; "[http ...]"; "[data ...]"; by creating 3 fucntions. Attached 1 at the bottom as reference. However, when I apply the function, some of the cell get the error (An error occurred in the ‘’ query. Expression.Error: The 'count' argument is out of range. Details: -2360)

May I know how can I solve this error? 

Remark:
Code of the fucntion learnt from another export: https://stackoverflow.com/questions/60640977/power-query-remove-text-between-delimiters

Error Message:

Function:

= (txt as text) =>
[
fnRemoveFirstTag = (HTML as text)=>
let
OpeningTag = Text.PositionOf(HTML,"[cid"),
ClosingTag = Text.PositionOf(HTML,"]"),
Output =
if OpeningTag = -1
then HTML
else Text.RemoveRange(HTML,OpeningTag,ClosingTag-OpeningTag+1)
in
Output,
fnRemoveHTMLTags = (y as text)=>
if fnRemoveFirstTag(y) = y
then y
else @fnRemoveHTMLTags(fnRemoveFirstTag(y)),
Output = @fnRemoveHTMLTags(txt)
][Output]

[Anonymous]

There's probably some limit, like 32,7xx characters.

But couldn't you use those tags as the delimiter, use Text.BetweenDelimiters to get your substrings, and then use those each of those substrings as a parameter to Text.Replace, and replacing with nothing?

Im not at my desk, but you seem to understand functions. Hopefully this gets you started, I'll be in later to check back.

--Nate

[kenchan]

Thank you for your insight. I try; I believe it solved part of my question. However, if the cell have happen the pattern twice, it may not able to handle at once time. Is there any fucntion which I can handle this? 

[PhilipTreacy]

Hi @kenchan 

Please provide your file or at least sample data to run this code against.  Hard to debug it without the data it's using.

regards

Phil

[kenchan]

Thank you for your response; I attached the link for the data.

Step to get the error:

I would like to remove the the strings( "[cid ...]"; "[http ...]"; "[data ...]") in each cells of the file. I simply import the file into power bi; copy and paste the function below and create a new col and apply the function. However, I encounter the error "The 'count' argument is out of range". Thank you for your help and support.

Link of Data 

the function for removing [cid ...]

Function:

= (txt as text) =>
[
fnRemoveFirstTag = (HTML as text)=>
let
OpeningTag = Text.PositionOf(HTML,"[cid"),
ClosingTag = Text.PositionOf(HTML,"]"),
Output =
if OpeningTag = -1
then HTML
else Text.RemoveRange(HTML,OpeningTag,ClosingTag-OpeningTag+1)
in
Output,
fnRemoveHTMLTags = (y as text)=>
if fnRemoveFirstTag(y) = y
then y
else @fnRemoveHTMLTags(fnRemoveFirstTag(y)),
Output = @fnRemoveHTMLTags(txt)
][Output]