Forum Discussion

andris_'s avatar
andris_
Resolver I
8 years ago
Solved

Power Query: remove all text between delimiters

Hey all,   I'm creating a report, which has a SharePoint list datasource. It has like ~5 columns, and it contains memorandums about meetings. There is a description column, which contains the memos...
Need Help
  • Stachu's avatar
    Stachu
    8 years ago

    you can use recursion in PowerQuery
    crate a blank query and paste this code into editor, then call this function on your HTML column

    (txt as text) =>
[ 
fnRemoveFirstTag = (HTML as text)=>
    let
        OpeningTag = Text.PositionOf(HTML,"<"),
        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]

    EDIT - typo in syntax

Stachu's avatar
Stachu
Community Champion
8 years ago

you can use recursion in PowerQuery
crate a blank query and paste this code into editor, then call this function on your HTML column

(txt as text) =>
[ 
fnRemoveFirstTag = (HTML as text)=>
    let
        OpeningTag = Text.PositionOf(HTML,"<"),
        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]

EDIT - typo in syntax

pAAmckelvey's avatar
pAAmckelvey
Frequent Visitor
6 years ago

Stachu ,

I'm new to calling functions on columns so please bear with me. 

The statement " then call this function on your HTML column" confuses me a little bit.   The query solution you provided has been created in my project and is called "RemoveHTML". 

 

The column with the HTML is called "Comments" and the table it is located in is called "Issues". 

 

Do I create a function from the Query first?  If so, the system wants me to create a parameter as it says the Query doesn't include a parameter.  

 

Not sure what my next steps are, any assistance is appreciated. 

 

Patrick

 

  • Stachu's avatar
    Stachu
    Community Champion
    6 years ago

    pAAmckelvey in the Issues table you need to create a new column with this syntax:

     

     RemoveHTML([Comments])

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I am just getting into functions myself and have been working on a problem to remove HTML tags from within a comments column.

      I tried using the Function as you outlined, but I keep getting an error that I am passing a parameter to a functions that expects 0 parameters.

      (1) In my steps, I created a blank query and copied your code into it.

      (2) I then created a function from the blank query.

      (3) Created a Custom Column and added the syntax you outlined in your response. (Probably where I am going wrong.)

      (4) It then returns the error I mention above.

      Any thoughts on what I might be doing wrong?

      Thanks

      Joe Mays

       

      • Stachu's avatar
        Stachu
        Community Champion
        6 years ago

        Anonymous can you create a new post describing specific problem that you have and share the code for all the queries that you use?