HTML format to text in tables

[Siboska]

Hi, 

I have some HTML columns in T-SQL that I'm importing into Power BI. I have created an HTML strip function in T-SQL that considers most of the signs. However, I cannot figure out how to keep the text formatting options such as bold (<strong>,</strong>). I have created a replace function that replaces "strong" with "b" as I thought Power BI was handling that as bold text. Unfortunately, it seems not to work. Does anyone have a suggestion for how I can obtain this?

(The HTML visual in Power Bi is not an option in this case)

[v-jianboli-msft]

Hi @Siboska ,

Here is a handy M Function for stripping all the HTML tags:

let func = (HTML) =>
    let
        Check = if Value.Is(Value.FromText(HTML), type text) then HTML else "",
        Source = Text.From(Check),
        SplitAny = Text.SplitAny(Source,"<>"),
        ListAlternate = List.Alternate(SplitAny,1,1,1),
        ListSelect = List.Select(ListAlternate, each _<>""),
        TextCombine = Text.Combine(ListSelect, "")
    in
        TextCombine, 
        documentation = [
            Documentation.Name =  " Text.RemoveHtmlTags"
            , Documentation.Description = "Remove Html Tags"
            , Documentation.LongDescription = " Removes all Html tags from a text"
            , Documentation.Category = " Text.Modification"
            , Documentation.Source = " Inspired by a solution from Bill Szysz"
            , Documentation.Author = " Imke Feldmann: www.TheBIccountant.com & Mike Carlo: PowerBI.Tips"
            , Documentation.Examples = {[
                Description = "Function that enables one to pass in a column that has HTML tags."
                , Code = "<div>my bit of text</div>"
                , Result = "my bit of text "
                ]}] 
 in 
    Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

Please try to create a new blank query and paste the above code into the advanced editor:

Then you can use this function directly:

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JJL]

Thanks for the solution to covert HTML contents to correct format in the Power BI table columns. 

I used it for couple columns in the table, it works great!

[Siboska]

Hi Jianbo, 

Unfourtenly it doesn't respect the text format option like bold, italic e.g.

[v-jianboli-msft · Accepted solution]

Hi @Siboska ,

According to my knowledge, Power BI won't retain information about fonts, bolding, etc. when fetching data from data sources.

If you want to change the format of your value, you could change the visual format:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.