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

Interkoubess's avatar
Interkoubess
Solution Sage
8 years ago

Hi andris_,

 

Please retry the last formula.

 

Thank you.

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])

         

         

    • 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.

       

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    8 years ago

    Hi andris_,

     

    Have you solved your problem?

     

    If you have soloved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

     

    If you still need help, you could have a reference of this video.

     

    Best Regards,

    Cherry