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 /: <...>

vsslasd1's avatar
vsslasd1
Helper III
6 years ago

I've read through this, and still a bit confused. 

I just want to add a new text column based upon an HTML-based column and remove the HTML tags as described. I'm not certain how to add a custom function. 

Can this all be done in one step, within the creation of the new column ? 

My new column name is: NoteText which is based upon the column NoteHTML

 

Thank you

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Add a function just like you would a standard query.

 

1.  Create a new blank query.  Name it "HTML Cleaner".

2.  In the Advanced Editor, paste the following:

 

(HTML as text) =>
let
Source = 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

 

3.  Close the Editor.

4.  In your primary query, open the Advanced Editor.

5.  Add the following step:

 

#"HTML Cleanup" = Table.AddColumn(#"Previous Step", "NoteText", each
if [NoteHTML] = null
then null
else #"HTML Cleaner"([NoteHTML])),

 

Good luck.

  • vsslasd1's avatar
    vsslasd1
    Helper III
    6 years ago

    Thank you, but unfortunately, no luck. I get over 4000 errors. 

    This is the syntax I have:

     

    let
    Source = OData.Feed("https://xyzasdf1111.api.crm.dynamics.com/api/data/v9.1", null, [Implementation="2.0"]),
    new_arcollectioncalls_table = Source{[Name="new_arcollectioncalls",Signature="table"]}[Data],
    #"Expanded new_currentowner" = Table.ExpandRecordColumn(new_arcollectioncalls_table, "new_currentowner", {"fullname"}, {"new_currentowner.fullname"}),
    #"Expanded new_BillingMgr" = Table.ExpandRecordColumn(#"Expanded new_currentowner", "new_BillingMgr", {"fullname"}, {"new_BillingMgr.fullname"}),
    #"Expanded new_arcollectioncall_Annotations" = Table.ExpandTableColumn(#"Expanded new_BillingMgr", "new_arcollectioncall_Annotations", {"createdon", "_ownerid_value", "notetext"}, {"new_arcollectioncall_Annotations.createdon", "new_arcollectioncall_Annotations._ownerid_value", "new_arcollectioncall_Annotations.notetext"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded new_arcollectioncall_Annotations",{"new_arcollectioncall_Annotations._ownerid_value"}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Removed Columns", {{"new_arcollectioncall_Annotations.createdon", type text}}, "en-US"), "new_arcollectioncall_Annotations.createdon", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"new_arcollectioncall_Annotations.createdon.1", "new_arcollectioncall_Annotations.createdon.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"new_arcollectioncall_Annotations.createdon.1", type date}, {"new_arcollectioncall_Annotations.createdon.2", type time}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"new_arcollectioncall_Annotations.createdon.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"new_arcollectioncall_Annotations.createdon.1", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([new_status] = true)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"new_legal", "_new_currentowner_value", "_ownerid_value", "new_collectionnotes", "new_collections", "_createdonbehalfby_value", "new_customer_number", "createdon", "owninguser", "owningteam", "ownerid", "new_arcollectioncall_Annotations.createdon.1", "new_arcollectioncall_Annotations.notetext", "new_BillingMgr.fullname", "new_currentowner.fullname"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"new_arcollectioncall_Annotations.notetext", "NoteHTML"}}),

    #"HTML Cleanup" = Table.AddColumn(#"Renamed Columns", "PlainNoteText", each
    if [NoteHTML] = null
    then null
    else #"HTML Cleaner"([NoteHTML]))
    in
    #"HTML Cleanup"

     

     

    And the "Blank Query" Definition: 

    "(HTML as text) =>#(cr)#(lf)let#(cr)#(lf)Source = Text.From(HTML),#(cr)#(lf)SplitAny = Text.SplitAny(Source,""<>""),#(cr)#(lf)ListAlternate = List.Alternate(SplitAny,1,1,1),#(cr)#(lf)ListSelect = List.Select(ListAlternate, each _<>"" ""),#(cr)#(lf)TextCombine = Text.Combine(ListSelect, "" "")#(cr)#(lf)in#(cr)#(lf)TextCombine"

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Unfortunately, without seeing the errors, and the data that generates them, I won't be able to help much further.  Perhaps you could create a 'dummy' table in the query, to include data rows that generate errors, and rows that don't.  Use the following code:

       

      let

      Table = #table( type table
      [
      #"NoteHTML" = text
      ],
      {
      {"Data that throws an error"},
      {"Data that throws an error"},
      {null},
      {"Data that doesn't"}
      }
      ),

      #"HTML Cleanup" = Table.AddColumn(Table, "PlainNoteText", each
      if [NoteHTML] = null
      then null
      else #"HTML Cleaner"([NoteHTML]), type text)

      in
      #"HTML Cleanup"