Power Query: remove all text between delimiters

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.

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

There is an even simpler solution.

You can create a new function called fun_ReplaceTextBetweenDelimiters, and in it add this code 👇

let
    fun_ReplaceTextBetweenDelimiters = (Text as text, StartDelimiter as text, EndDelimiter as text, optional ReplaceDelimiters as nullable logical, optional NewText as nullable text, optional TrimResult as nullable logical, optional FixDoubleSpaces as nullable logical) as text => 
        let
            // Add Default Parameters
            Default_ReplaceDelimiters = if ReplaceDelimiters is null then true else ReplaceDelimiters,
            Default_NewText = if NewText is null then "" else NewText,
            Default_TrimResult = if TrimResult is null then true else TrimResult,
            Default_FixDoubleSpaces = if FixDoubleSpaces is null then true else FixDoubleSpaces,
            //Do work
            TextBetweenDelimiters = Text.BetweenDelimiters(Text, StartDelimiter, EndDelimiter),
            TextToReplace = if Default_ReplaceDelimiters then Text.Combine({StartDelimiter,TextBetweenDelimiters,EndDelimiter}) else TextBetweenDelimiters,
            ReplacedText = Text.Replace(Text, TextToReplace, Default_NewText),
            //Clean Result
            TrimmedText = if Default_TrimResult then Text.Trim(ReplacedText) else ReplacedText,
            FixedSpaces = if Default_FixDoubleSpaces then Text.Replace(TrimmedText, "  ", " ") else TrimmedText
        in
            FixedSpaces
in
    fun_ReplaceTextBetweenDelimiters

Then, we can test it like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtAw1FTSAbGUYnWilVzd3BU0jEAiQBZYJCIyCqhGRwEsCOQoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TestData = _t, TargetData = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"TestData", type text}, {"TargetData", type text}}),
    RunFunction = Table.AddColumn(ChangeType, "NewText", each fun_ReplaceTextBetweenDelimiters([TestData], "(", ")", true), type text),
    TestResult = Table.AddColumn(RunFunction, "Test", each [TargetData]=[NewText], type logical)
in
    TestResult

Input:

Output: