Power Query can import table-based data direct from the web. As an example, we can extract the public holiday dates for Western Australia from the WA government website. This process is sometimes called web scraping.

Note if you want to use this technique long term it assumes the location of the table plus the table structure remain constant over time.

As well as the public holiday date we can also capture the observed date if they are different. The observed date is the date when the celebration is held, not the weekday holiday date.

The example file can be downloaded at a button at the bottom of the post.

The table we want is shown below. The web address is.

https://www.wa.gov.au/service/employment/workplace-arrangements/public-holidays-western-australia

To extract the table we can use the From Web icon on the Data ribbon tab.

Copy and paste the URL into the URL box and click OK.

In the dialog that opens find and select the table you want to import. In this case it is the first table listed.  Click the Transform Data button (bottom right).

The structure of the table is shown in the image below.

I recommend deleting the Changed Type step on the right as it isn’t required. Click the X on the left of the step.

Also rename the Query with a shorter name. The Query name will become the sheet name. You can change the sheet name after the query is run if you like.

Note we rarely call Western Australia by its full name. In the great Australian tradition, (we shorten everything), so we always call our state WA.

We can capture the column headings (called promoting the headers). We use the icon on the Home ribbon called Use First Row as Headers.

Again, a Changed Type step is automatically added – I recommend deleting it.

Rename the first column to Holiday. Double click the Header and change the name.

Right the first column Header row and choose Unpivot Other Columns.

This re-organises the data as shown below. It creates what is called a “normalised”  data structure.

When it does this, the column headers are always listed under the Attribute name and the individual entries are listed under Value.

This structure is a more efficient way to display the data. In the original table the information was laid out like a report. Instead of separate columns for the year it is more efficient to have a single column for the year with values representing each year.

Observed dates

In some years some holidays have an observed date and a public holiday date. When this happens, the observed date is always listed first – see example below.

We need to separate these. Luckily there is a “&” character between the two dates. This means we can split the column into two columns using that character as a delimiter.

Right click the value column and chose Split Column then click By Delimiter.

Click the top drop-down and choose Custom.

Type the & symbol in the box and click OK.

Select the Holiday column and hold the Ctrl key and select the Attribute column. Release the Ctrl key and right click one of those columns and choose Unpivot Other Columns. The image below shows the result. Delete the Attribute.1 column.

We will keep the Value column and duplicate it. We can then eventually convert the duplicated column into a real date.

Right click the Value column and choose Duplicate Column.

Right click the new column and choose Split Column, then by By Delimiter.

In the dialog choose Space in the top drop-down and tick the Left-most delimiter option and click OK.

Power Query will try to help and change the data type to date and all the dates will be in 2025 – see image below.

Delete the last Change Type step.

Click the Add Column ribbon tab and choose the Custom Column icon.

Create the following formula in the Custom column formula box.

 =[#"Value - Copy.2"] & [Attribute]

You can double click the column  headings on the right of the dialog to insert them in the formula. The & symbol joins the text together. Click OK.

The new column looks like this.

This is close enough to a date for Power Query to convert it into a date. Click the small icon on the left of the Custom column header and choose Date. Rename the column to Date.

Delete the Value – Copy.2 column and rename the other columns as below.

That’s it – the listing is now complete click the Close and Load icon on the Home ribbon tab.

For this to keep working the table needs to be maintained on the website and the website address needs to be the same.

Relying on websites to remain the same has its issues and you need to be aware that this query could crash in the future. Be prepared to have to re-build it.