Convert HTML column to plain/formatted text in Power BI

[sardo]

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.

[Anonymous]

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]

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.

[Sahir_Maharaj]

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]

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?