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"

[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? 

[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.