Forum Discussion
Power Query - HTML
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!!!!!
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.
2 Replies
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.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.
