Power Query - HTML

[Anonymous]

Hi, 
I want to extract the text from html retaining the formatting. I used html.table function but it is not giving the desired output
input:
<ol style="list-style-image: none; list-style-type: decimal;"><li>Test by me</li><li>Testing</li></ol>
output:( when I used html.table it's givig output as below)
Test by meTesting

expected output:

1)Test by me

2)Testing

Thanks in Advance!!!!!

@lbendlin @bcdobbs @parry2k @Greg_Deckler @PhilipTreacy 

[Anonymous · Accepted solution]

Hi @Anonymous ,

1. Click the column--> Split by Custom delimiter "<li>"

2. Select the two new columns--> Extract between delimiters:

3. Then you could remove the original column and Transpose the columns.

Here is the whole M syntax:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiik1MDBOzs9RKC6pzEm1jVHKySwu0QVzdDNzE9NTrRTy8vNSrRWQxEsqC4DCKanJQAU51jEQM1IhJuVkQnghqcUlCkmVCrlQcX2YBKayzLx0rGr083MgXKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("<li>", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Split Column by Delimiter", {{"Column1.2", each Text.BeforeDelimiter(_, "</li>"), type text}, {"Column1.3", each Text.BeforeDelimiter(_, "</li>"), type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Text Before Delimiter",{"Column1.1"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns")
in
    #"Transposed Table"

Final output:

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

[edhans]

Before parsing the data, you could replace all </li> strings with ***</li>, then use the Split Columns feature on the *** string.

Other that that, I'd need to see some data because you've  not given enough to use the Html.Table function in the above post.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.