Column in xml format convert it to readable format.

[Blue_BIGuy]

Hello All, 

I am extracting the data from SQL DB, one of the column of the table contents data in below (As it) format. 

Kindly guide, how can I achieve (To Be) format. 

As is		To Be
Comment 1		Comment 1
<div class="ExternalClass81BC5B248F2E4A09BB29988B0773CC3D"><br>Hello Team,Testing the property,from IT Depart<br><strong>(Approved on 26/03/2023 17&#58;54)<br><span>Please proceedGet Outlook for iOS</span></strong><br></div>		Hello Team,Testing the property,from IT Depart (Approved on 26/03/2023 17&#58;54) Please proceedGet Outlook for iOS
<div class="ExternalClassCFAB999A270D4772897D8937030C0FF2"><br>ABCD ,Senior Manager, IST,  Dept<br><strong>(Rejected on 26/03/2023 17&#58;20)<br><span> Please use the correct appendix Get Outlook for iOS</span></strong><br></div>		ABCD ,Senior Manager, IST,  Dept (Rejected on 26/03/2023 17&#58;20) Please use the correct appendix Get Outlook for iOS
<div class="ExternalClass07A7D923F6C649179C7C24CA78BBA634"><br>XYZ,Vice President, Communications,Retail Management Dept<br><strong>(Approved on 27/03/2023 00&#58;07)<br><span>UVZ Vice President,(Company Registration No. 1234323)1 #05-1089, Australia 098675Tel&#7798; 9189278356Fax&#85;92384624089</span></strong><br></div>		XYZ,Vice President, Communications,Retail Management Dept(Approved on 27/03/2023 00&#58;07) UVZ Vice President,(Company Registration No. 1234323)1 #05-1089, Australia 098675Tel&#7798; 9189278356Fax&#85;92384624089

Thanks in advance.

[v-jianboli-msft · Accepted solution]

Hi @Blue_BIGuy ,

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.

[Blue_BIGuy]

Thanks @v-jianboli-msft