topic Re: How to use Html.Table properly? in Power Query

How to use Html.Table properly?

Sica — Thu, 19 Aug 2021 15:16:54 GMT

I have a column which contains several rows with HTML code, like the example below:

<thead>

<th scope="col">Number</th>

<th scope="col">Version</th>

<th scope="col">Group</th>

</tr>

</thead>

<tbody><tr style='background-color: lightgray;'><td><a href="http://mysite.com/httpconnect/connect.html?M=CM?ID=D1862842" target="_blank">D42</a></td><td><a href="http://mysite.com/httpconnect/connect.html?M=IM?No=24035401" target="_blank">401</a></td><td>MARIA</td><td>003</td><td></td><tr><td><a href="http://mysite.com/httpconnect/connect.html?M=CM?ID=D1862842" target="_blank">D42</a></td><td><a href="http://mysite.com/httpconnect/connect.html?M=IM?No=24035402" target="_blank">402</a></td><td>MARIA</td><td>P03</td><td>3741</td><tr style='background-color: lightgray;'><td><a href="http://mysite.com/httpconnect/connect.html?M=CM?ID=D1862842" target="_blank">D42</a></td><td><a href="http://mysite.com/httpconnect/connect.html?M=IM?No=24035403" target="_blank">403</a></td><td>MARIA</td><td>P03</td><td>3741</td><tr><td><a href="http://mysite.com/httpconnect/connect.html?M=CM?ID=D1862842" target="_blank">D42</a></td><td><a href="http://mysite.com/httpconnect/connect.html?M=IM?No=24035404" target="_blank">404</a></td><td>MARIA</td><td>P03</td><td>3741</td><tr style='background-color: lightgray;'><td><a href="http://mysite.com/httpconnect/connect.html?M=CM?ID=D1862842" target="_blank">D42</a></td><td><a href="http://mysite.com/httpconnect/connect.html?M=IM?No=24035405" target="_blank">405</a></td><td>MARIA</td><td>P03</td><td>3741</td></tbody></table>

How are I able to extract information I want from the code above by using Html.Table?!?

Re: How to use Html.Table properly?

AlexisOlson — Thu, 19 Aug 2021 16:00:51 GMT

If you connect to this as a web page, then you use the "Extract table using examples" tool. This returns code like this:

Html.Table( Source, {{"Column1", "TABLE > * > TR > :nth-child(1)"}, {"Column2", "TABLE > * > TR > :nth-child(2)"}, {"Column3", "TABLE > * > TR > :nth-child(3)"}, {"Column4", "TABLE > * > TR > :nth-child(4)"}, {"Column5", "TABLE > * > TR > :nth-child(5)"}}, [RowSelector="TABLE > * > TR"] )

You can then promote headers to get

Now I'm trying to figure out how to use that tool without connecting to a website...

For reference, I found this article useful:
https://www.thepoweruser.com/2018/05/14/new-web-scraping-experience-in-power-bi-power-query-using-css-selectors/

Re: How to use Html.Table properly?

Sica — Thu, 19 Aug 2021 16:13:39 GMT

It works beautifully!!!

It is weird, but it adds an extra line containing the same descriptions that are available in the headers. Instead of 5 rows as output, I have 6, where the first one contains the same values already available in the headers. The only change I did is to replace the Source by a column:

= Table.AddColumn(#"Changed Type", "Info Table", each Html.Table([Customer Information], ...).