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_,

 

I will rather use something like Text. Start(Text.Range etc..)

 

You can make a try with this formula and if it does not work please provide example I can copy easily and make the change ( not printscreen).

Text.Range([Description],Text.PositionOf([Description],"=")+2,Text.PositionOf([Description],"<p>")-Text.PositionOf([Description],"=")-4

Hope it helps...

 

Ninter

  • 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