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

Hi Interkoubess,

 

Thank you for your reply!

 

I think Text.Start can be a solution as well, but my focus is on the iteration part, because as you can see in my sample dataset, there are more than one delimiters in a record. What I'd like to do is to remove all the html elements, and keep/concatenate the TEXT parts.

 

With the following formula, you can have the same result, as yours:

Text.BeforeDelimiter([Description],"<p>")

Tell me if I misunderstood You, but I can't see how can I do it in an iterative way.

 

(Here's some sample data you can use)

IDTopicCustomerDescriptionStatus
1Implementation of XYCustomer 1<div class="ExternalClass6F25214767CC48ADBB6B525F221F78B4"><p>TEXT </p><p>&#160;</p><p>TEXT</p><p>TEXT</p>TEXT<strong>TEXT</strong></li></ul></div>Opened
2Product ManagamentCustomer 2<div class="ExternalClass45BB2C5321C95J49B0F78879862B8310C"><p>TEXT</p><p>TEXT</p><strong>TEXT</strong></p></div>Closed
3Exchange MachinesCustomer 1<div class="ExternalClassEC3F6FC6C1D64D78980B1B5FF615B9860"><p>TEXT</p></div>Opened
4No.: 104085; checking statusCustomer 3<div class="ExternalClassDC1495BEBABE408B9855E7DDF1B1D6AD"><p>TEXT</p><p>TEXTOpened
5Backup machinesCustomer 4<div class="ExternalClass7EB7A021D123446BCA2D83011F9CC301C"><p>TEXT</p><p><br></p><p>TEXT</p><p>TEXT</p></div>Closed

 
Regards,
Andris

 

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

    • 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