Extract data from html table

[JohnBlue1]

Dear All,

Good day.

I have a table with 2 columns

1) primary ID

2) html of a table

I want power qwery to return the table in the html & the primary ID.

Exemple:

line 1

primary ID 1

<table>
<tr>
<td>Alfreds Futterkiste</td>
<td>Maria Anders</td>
</tr>
<tr>
<td>Centro comercial Moctezuma</td>
<td>Francisco Chang</td>
</tr>
</table>

I want power qwery to return:

1 Alfreds Futterkiste Maria Anders

1 Centro comercial Moctezuma Francisco Chang

How can I do so?

Kind regards,

[Syndicate_Admin]

Hi @JohnBlue1 ,

You said that your table has two columns. Sorry, from your example I can’t see how the two columns are.

Please describe in detail how the two columns are and what the expected result is.

Best Regards,

Stephen Tao

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

[Anonymous]

Hi @JohnBlue1 ,

You said that your table has two columns. Sorry, from your example I can’t see how the two columns are.

Please describe in detail how the two columns are and what the expected result is.

Best Regards,

Stephen Tao

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

[Vera_33]

Hi @JohnBlue1 

How is the data coming in? Text, binary? you said a table with 2 columns?

HTML part you can use Web.Page, one sample coming in as text, you can take from here

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBRCsIwEESvsvQCrXiCUin40RO0/Vg3qwaTjWySDz29YFCkUvRv3zLDDDOOlbPCsJlkkqtaj3qD/a7wlJtmSwkPjp8nv1+6YFO4dUdlE6HPKbFebEzFSPVLsXQMqBahFcMaV6T1V9pKeseSNAAFz0oWHQyBEt+zxx8lekUhGylAd0Y5/duj/limmucH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Web.Page([Column1])),
    Custom = Table.SelectRows( #"Added Custom"{0}[Custom], each [Source]="Table")[Data]{0},
    #"Changed Type" = Table.TransformColumnTypes(Custom,{{"Column1", type text}, {"Column2", type text}})
in
    #"Changed Type"