Forum Discussion
How to use Html.Table properly?
I have a column which contains several rows with HTML code, like the example below:
<table style='border-collapse: inherit; overflow-x: scroll; display: inherit; white-space: nowrap; width: -moz-available; width: -webkit-fill-available;'>
<thead>
<tr style='background-color: darkgray;'>
<th scope="col">ID</th>
<th scope="col">Number</th>
<th scope="col">Name</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?!?
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/
2 Replies
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/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], ...).
