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

andris_'s avatar
andris_
Resolver I
8 years ago

Hey Interkoubess,

 

This formula (with minor changes) is working, but it will only give you the first text. The problem is, that there are more between in a record, and every record is different (so, like in my sample data, the first record has 5 TEXT parts, the second one 3 TEXT parts etc).

 

What do you think the general solution should be?

 

Regards,

Andris

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

  • andris_'s avatar
    andris_
    Resolver I
    8 years ago

    Hey Stachu,

     

    Sorry for not replying until now, but I wasn't able to check it til today. But your solution is awesome, it's working! Thank you! :)

     

    Best regards,

    Andris

  • 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

         

  • Gill's avatar
    Gill
    New Member
    3 years ago

    Hi Stachu , I'm new to using power query to extract data from sharepoint.

     

    I followed your code to remove all the HTML but there is this error msg. Appreciate your help on this.

     

  • lawrence_sproul's avatar
    lawrence_sproul
    New Member
    1 year ago

    This is nice, but null values in the column produce errors.