Convert data from HTML into plain text in a table-...

Fair-UL · ‎11-12-2019

I am bringing field from a SQL table- Azure that is saved in rich text format. When thos field is brought into a table visual, it is not presented in simple text format. I tried several workaround I searched, but nothing worked. Any solution to this?

e.g. next milestone due Nov 12

I want it to be shown:

next milestone due Nov 12

Anonymous · ‎11-13-2019

Hi @Fair-UL ,

I think to replace tags not a simple way to handle this scenario, it means you need to configure a dictionary table with all HTML tags and use it in replace functions. (in addition, it also hard to find out specific text from replaced text value)

In my opinion, I'd like to you can try to add a custom column with HTML connector(Html.Table) to receive these string that contains HTML elements and extract specific characters from the result table.

Removing HTML Tags From Text In Power Query/Power BI
Regards,

XIaoxni Sheng

Fair-UL · ‎11-13-2019

Thank you for your response. I have seen this solution but I had difficulty implementing it.

let source= "[detail_text]",
removetags= Html.Table(source, {{"text", ":root"}}),
GetText= removetags [text] {0}
in
GetText

this is what I used in custom column, but its returning the text [detail_text] in all fields.

detail_field is the column header of the column that contains html text coming from the database

the address of server is (xxxhub-prod.database.windows.net), and the database is xxx

not sure how to implement this code you advised to use

let

Source =

"<!DOCTYPE html>

<html>

<body>

<h1>A Heading</h1>

A paragraph

</body>

</html>",

RemoveTags = Html.Table(Source, {{"text",":root"}}),

GetText = RemoveTags[text]{0}

in

GetText

Anonymous · ‎11-12-2019

@Fair-UL,

You can replace and with blank space in Query Editor using Replace Values transformation.

Fair-UL · ‎11-13-2019

I started with this approach, but there were too many of them. the html library is huge to be cleaned using replace function.

there is so many <li>, </li>, , , </ol>, etc

but thanks for the idea. might help other readers.

Anonymous · ‎11-15-2019

Hi @Fair-UL ,

In fact, it requires you to typing the 'column selector' parameter in HTML.table to extract specific element values.

I create a table with 'index' and 'html' strings, I add a custom column store 'h1' element that extracts from 'body' element, below is the query table formula and snapshot of the result:

let
    Source = #table({"Index","HTML"},
    {
        {1,"<!DOCTYPE html><html><body><h1>A Heading</h1><p>A paragraph</p></body></html>"},
        {2,"<!DOCTYPE html><html><body><h1>B Heading</h1><p>B paragraph</p></body></html>"},
        {3,"<!DOCTYPE html><html><body><h1>C Heading</h1><p>C paragraph</p></body></html>"}
    }),
    #"Added Custom" = Table.AddColumn(Source, "H1", each Html.Table([HTML],{{"body","h1"}})[body]{0})
in
    #"Added Custom"

Reference links:

Web Scraping with Html.Table in Power Query

New Web Scraping experience in Power BI / Power Query (Using CSS Selectors)

Expressions, values, and let expression

If you still confused about extract specific elements, you can also share some sample data and expected result for test. (do mask on sensitive data before share)

Regards,

Xiaoxin Sheng

Fair-UL · ‎11-15-2019

Hi,

Thank you for your illustration, however, it is still confusing to me.

The field that I have is a free text field and users input string in and I am pulling this field into a table in power BI.

The way it looks in power BI depends on any spaces, enter, numbering the users have used while inputting their text

can include any of the html tags in between text

Anonymous · ‎11-21-2019

HI @Fair-UL ,

If your text contains randomly HTML tag, maybe you can try to use the following custom function to remove all characters that package with '<>'.

Recursive custom function:

let
    RemoveTag = (input as text) as text =>
    let    
        length = Text.Length(input),
        position = Text.PositionOf(input, "<"),
        positionEnd = Text.PositionOf(input, ">"),
        range = positionEnd-position+1,
        replaced = if position >= 0 then Text.ReplaceRange(input, position, range, "") else input,
        result= if Text.Contains(replaced,"<") then RemoveTag(replaced) else replaced
    in
        result
in
    RemoveTag

Usage:

let
    Source = RemoveTag("<!DOCTYPE html><html><body><h1>A Heading</h1><p>A paragraph</p></body></html>")
in
    Source

Reference link:

Robust function to remove HTML tags

If above not help, can you please share some sample data as table format for test?

Regards,

Xiaoxin Sheng

vsslasd1 · ‎06-05-2020

test

Fair-UL · ‎11-25-2019

Hi Xiaoxin,

Can you please provide some help with this sample data. The data has random html tags that not only include <xxx> but also tags like &nbsp

Column_Text

Updated Quick Task Model

need to update some missing trainings

initial response due Nov 11

in process

video in process, plan to launch Nov 14

Anonymous · ‎11-25-2019

HI @Fair-UL ,

Maybe you can refer to v-yuezhe-msft 's suggestion to do these conversions on SQL side then load the converted data to power bi:

I have a column in DQ that is stored in HTML format
Regards,

Xiaoxin Sheng

Convert data from HTML into plain text in a table- Data from SQL table

Helpful resources

Power BI Dataviz World Championships

Power BI Monthly Update - December 2025

FabCon Atlanta 2026

How to Get Your Question Answered Quickly

FabCon is coming to Atlanta

Convert data from HTML into plain text in a table- Data from SQL table

Helpful resources

Power BI Dataviz World Championships

Power BI Monthly Update - December 2025

FabCon Atlanta 2026

How to Get Your Question Answered Quickly