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

vgangabharani's avatar
vgangabharani
Frequent Visitor
6 years ago

New source->New blank query-> Highlight the fx (to convert it into function)
Function 1 to remove all HTML Tags

(txt as text) =>
[
fnRemoveFirstTag = (HTML as text)=>

let
TextBetween=Text.BetweenDelimiters(HTML,"<",">"),
Output=if Text.Length(TextBetween)>0 then

Text.Replace(HTML,
Text.Insert(
Text.Insert(TextBetween,0,"<"),
Text.Length(TextBetween)+1,">")
,"")
else
HTML
in
Output,
fnRemoveHTMLTags = (y as text)=>
if y<>null then
if fnRemoveFirstTag(y)<>y then
@fnRemoveHTMLTags(fnRemoveFirstTag(y))
else
y
else "",

Output =if txt <> null then @fnRemoveHTMLTags(txt) else ""
][Output]


Function 2 to remove encodings

let
Source = (txt as text) =>
[

fnRemovecodes = (y as text)=>
Text.Replace(Text.Replace(
Text.Replace(Text.Replace(Text.Replace(
Text.Replace(Text.Replace(
Text.Replace(Text.Replace(
Text.Replace(Text.Replace(
Text.Replace(
Text.Replace(y,"&apos;","'"),
"&lt;","<"),
"&gt;",">"),
"&quot;",""""),
"&amp;","&"),
"&nbsp;"," "),
"&copy;","©"),
"&reg;","®"),
"&cent;","¢"),
"&pound;","£"),
"&yen;","¥"),
"&euro;","€"),"&#39;","'"),


Output =if txt <> null then @fnRemovecodes(txt) else ""
][Output]
in
Source


To apply to the column of your table

- Go to the table, clock on the column
- click on Add column-> Invoke custom function

 

Give  aname to new column. Select the function1 and for txt select the column that you need to convert from HTML to text. Then on the new column select function 2 and for txt select the new column from previous step.