Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Remove HTML tags from columns

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"

 

Need Help
  • AlB's avatar
    AlB
    3 years ago

    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.

     

10 Replies

  • Mahesh0016's avatar
    Mahesh0016
    Super User
    3 years ago

    Anonymous ,

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      offenRong
      New Member
      1 year ago

      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"}})

       

  • Sang10's avatar
    Sang10
    Regular Visitor
    1 year ago

    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

  • AlB's avatar
    AlB
    Community Champion
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        AlB
        Community Champion
        3 years ago

        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.

         

  • v0ry's avatar
    v0ry
    New Member
    1 year ago

     

     

    And 

     

     

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