Automating extraction of table from HTML

[ConfusedTurtle2]

Hi all,

I'm using an API to get data from statistics.gov.scot into Power BI. The second applied step after importing the data, 'Extracted Table from Html', uses a script like the following:

= Html.Table(Source, {{"Column1", "TABLE.clay_cube_slice > * > TR > :nth-child(1)"}, {"Column2", "TABLE.clay_cube_slice > * > TR > :nth-child(2)"}}, [RowSelector="TABLE.clay_cube_slice > * > TR"])

The issue is when this dataset is updated the latest value is a new column which is not pulled in. I realise this is because the above query is manually selecting a set amount of columns and this can be fixed by adding the new column to this script each month, but for the purposes of what I'm doing it would be much better if I could simply refresh my data and have it automatically include the latest available data. That being the case is there a way to automate the above process so it will just take all columns, even as more are added as the data is updated?

Thanks

[lbendlin · Accepted solution]

Since they have an API you may just as well use it, instead of laboring around with screen scraping.

API - statistics.gov.scot Open Data User Guides