Remove HTML tags from columns

[Anonymous]

Hello,

I want to remove the HTML tags from my text columns, I tried multiple solutions on the community forum but even when I get something working I get an error in my custom column. The error is "Cannot convert a value of type Record to type Text".

I tried this:

Text.Clean(
Text.Trim(

if Text.Contains([[ALL.achievements_last_week]], "<")
then Text.BeforeDelimiter([[ALL.achievements_last_week]], "<")
else
[[ALL.achievements_last_week]])
)

and

=Html.Table([ALL.achievements_last_week], {{"ExtractedText",":root"}})

I still get the error "Cannot convert a value of type Record to type Text"

[v0ry]

And 

= Table.TransformColumns(#"Changed Type", {{"Column1.instructions", fnStripHTML, type text}})

[Sang10]

Anyone know how to get rid of the errors for the nulls, I can't remove the errors as there is other info attached to that row

[Anonymous]

Background: ADO > Boards SVC > Query > "Open in Power BI" Extension. All the custom fields are exporting as HTML. After performing the aforementioned guidance, which partially works in my case, the null fields are causing Errors. Any advice on a best way to remove Nulls or ignore them? 

[offenRong]

I found that using an if statement to only apply Html.Table to non-null values did the trick when expanding the column.

e.g. 

if 
[Column1] = null 
then null 
else Html.Table([Column1], {{"ExtractedText",":root"}})

[Mahesh0016]

@Anonymous ,

>> If this post helps, please consider accept as solution to help other members find it more quickly.

[AlB]

Hi @Anonymous 

Can you share the query or, ideally, a pbix reproducing the error?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous]

Hi @AlB Please find the example pbix

https://we.tl/t-d4G1yq5Qw7 

One note I also tried the "HTML Content" visual but its not working with it as well.

[AlB · Accepted solution]

@Anonymous 

You were almost there. I believe there's only a simple error in your code for the custom column. You had:

Html.Table([[Column1]], {{"ExtractedText",":root"}})

 with  [[Column1]] where you only need one set of [ ]:

Html.Table([Column1], {{"ExtractedText",":root"}})

That is why PQ was interpreting as a record

See it at work in the attached file (Table1, I've left your original query untouched)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous]

Thank you @AlB That did the trick. Didnt notice the second pair of [ ] 🙂 

[Mahesh0016]

@Anonymous 

If this post helps, please consider accept as solution to help other members find it more quickly.