Forum Discussion

sardo's avatar
sardo
Regular Visitor
2 years ago

Convert HTML column to plain/formatted text in Power BI

I need to conver HTML column in plain text. I've watched some tutorials and added a custom column with the following coding in Power Query: Html.Table([HTML text],{{"Plain text",":root"}})

This does not work for me, as I have some empty cells (null) in the "HTML text" column and causes an error when creating and trying to expand the table.

I also tried to conver the HTML column into a formatted text (instead of plain) and didn't work.

 

Hope you can help me with this, thank you.

Need Help
Tips and Tricks

5 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    2 years ago

    Hello sardo,

     

    Can you please try the following:

     

    1. Before converting HTML to text, you should replace or remove null values to prevent errors during the conversion process (Use the "Replace Values" in Power Query)

    2. Extract text from the HTML using M code

    let
    HtmlToText = (html as text) as text =>
    let
        // Replace HTML tags with empty strings
        Step1 = Text.Replace(html, "<", " <"),
        Step2 = Text.Split(Step1, "<"),
        Step3 = List.Transform(Step2, each if Text.StartsWith(_, "/") or Text.Contains(_, ">") then "" else _),
        TextResult = Text.Trim(Text.Combine(Step3, ""))
    in
        TextResult
in
    HtmlToText

    3. Use the custom function you've defined by referencing your HTML column: HtmlToText([YourHtmlColumnName])

     

    Note: If your conversion results in a table, you might need to expand it. Hope this helps.

    • sardo's avatar
      sardo
      Regular Visitor
      2 years ago

      Hi Sahir, 

      Thank you so much for your response.

      Can you please guide me where can I add that code in the "Replace Values" in Power Query?

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi sardo 

     

    Can you provide some dummy data to show what you want to achieve? Perhaps you want to extract a table between <table> tags from the HTML code? Or perhaps you want to remove all tags in some HTML code? Do you have HTML code in the [HTML text] column or have a html file in this column?

     

    Best Regards,
    Jing

    • sardo's avatar
      sardo
      Regular Visitor
      2 years ago

      Hi Jing,

      I'm connected to Salesforce Object and this is an HTML field.

      When I use the HTML Content Custom Visual, it shows me the data correctly with the right formatting, but I can't add any more fields and I need the content to be used in a Table. 

      I successfully added a custom column in Power Query by using the Html.Table([HTML text],{{"Plain text",":root"}}) code, but it's a plain text and hard for the customer to read.

       

       

       

      Hope this clarifies. Thank you.

  • navd36_bi's avatar
    navd36_bi
    New Member
    9 months ago

    Hi,

    In your transformation, go to column which you want to clean.

    Insert step to replace  the null values to

    Blank ""

    Add custom column for following:

    Html.Table([HTML text column],{{"text",":root"}})