Forum Discussion

kenchan's avatar
kenchan
Frequent Visitor
4 years ago

Remove Text Between - Power BI

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...
Need Help
PhilipTreacy's avatar
PhilipTreacy
Super User
4 years ago

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's avatar
    kenchan
    Frequent Visitor
    4 years ago

    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]