Forum Discussion

unicorn's avatar
unicorn
Regular Visitor
9 years ago
Solved

Robust function to remove HTML tags

Hi, I have a column of HTML data that I need to display as text. I need to get rid of all HTML tags and substitute reserved HTML characters. I was trying to recursively remove all HTML tags first. Th...
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    Just change the "|" into "<"

     

    rmvAll = if Text.PositionOf(rmvOne, "<") >= 0 then @removeAll(rmvOne) else rmvOne

     

    Otherwise you may get unexpected results if the string contains < or > that are not part of a HTML tag.

     

    If you are interested, I can share my code that only removes tag pairs, i.e. </...> strings preceded by the same without /: <...>

Grumelo's avatar
Grumelo
Advocate II
8 years ago

This solution is capable of removing tags like

<p> </p>

but not something like

<div class="..."> </div>

 

 

An example of function call would be welcome. I don't understand what the meaning of "Iteration".

Is it the amount of tags you want to remove?

Grumelo's avatar
Grumelo
Advocate II
8 years ago

My solution to clean HTML tags

 

let
   TextFromHtml = (HTML as any) =>
let
    Source = if HTML = null then
        ""
    else
        Text.From(HTML),
    SplitAny = Text.SplitAny(Source,"<>"),
    ListAlternate = List.Alternate(SplitAny,1,1,1),
    ListSelect = List.Select(ListAlternate, each _<>""),
    TextCombine = Text.Combine(ListSelect, "")
in
    TextCombine
in
    TextFromHtml

For beginners: create a blank query an copy paste the code above; rename the query as "TextFromHtml"

 

Example of calling this function on a column called Comment:
= Table.TransformColumns(#"Previous Step",{{"Comment", TextFromHtml, type text}})

 

Reference: https://social.technet.microsoft.com/Forums/office/en-US/b080d122-14f0-43bc-8a86-f50cdf1c32d8/removing-html-tags-when-source-is-sharepoint-list?forum=powerquery&prof=required

  • ScottTynan's avatar
    ScottTynan
    Frequent Visitor
    3 years ago

    Thanks Grumelo, your response solved my issue!

     

  • sizi's avatar
    sizi
    Helper II
    2 years ago

    Hello. I tried your query, but its giving me error as below:

    Expression.Error: We cannot convert the value "<div>The business ca..." to type Table.
    Details:
    Value=<div>The business case was delayed, as the proposals were not submitted by the vendors ontime</div>
    Type=[Type].

    This is the custom function : NoteText= Table.AddColumn(#"Changed Type4", "NoteText", each Table.TransformColumns([#"Notes/Comments"],{{[#"Notes/Comments - Copy"], TextFromHtml, type text}})) . Kindly help as my data is as below which varies with every comment :   

    <div class="ExternalClassAEC9E21CD9CB4D5099FAB2EBE2BC4A87"><div style="font-family&#58;Calibri, Arial, Helvetica, sans-serif;font-size&#58;11pt;color&#58;rgb(0, 0, 0);">Final sign off confirmation received today from BRM. Attached the confirmation email.</div></div>

     

    <div class="ExternalClass82C655AC166D45B68A0237698583C179"><div style="font-family&#58;Calibri, Arial, Helvetica, sans-serif;font-size&#58;11pt;color&#58;rgb(0, 0, 0);">PQD provided the focal point list yesterday.&#160;Meeting to be scheduled as per the Business availability to revalidate the BRD.&#160;&#160;</div></div>

     

    Comments are as above for reference. I need the actual text from these comments. 

     

    Thanks,