Forum Discussion
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...
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
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
Hi Stachu , I'm new to using power query to extract data from sharepoint.
I followed your code to remove all the HTML but there is this error msg. Appreciate your help on this.
hi Gill
it seems you've put the code into the formula bar rather than an advanced editor:
https://learn.microsoft.com/en-us/power-query/power-query-ui#the-advanced-editor