Power Query: A Tangled Web Part 3

Welcome to our Power Query blog.  This week, I continue looking at the web data I extracted to the Power Query editor.

When extracting data from the web Power Query utilises M code to access the data in a form that can be used in reports.  Over the next few blogs, I am planning to examine the M code produced.  The data I am going to use to investigate web data is our very own website:

This page contains a table of upcoming courses, along with various buttons and text:

In Part 1, I used the web connector to examine the data in the Navigator dialog.

I also created more tables using the ‘Add Table Using Examples’ functionality. 

I found that I cannot combine data from inside a table and data outside of that table in the same custom table.  I therefore added two [2] custom tables:

The training contact data has been captured in Table 2 but not in Table 1.

Last time, I loaded all the queries into the Power Query editor: 

I examined the M code in the HTML Code and Displayed Text queries.  I found that HTML code used the M function Web.BrowserContents(), and Displayed Text began in the same way, but went on to use the Html.Table() function too.

This time, I will look at the remaining queries: Upcoming Courses, Table 1 and Table 2.  Let’s start with Upcoming Courses:

This is the query for the table that was extracted automatically into the Navigator dialog from the webpage.  The Source step is the same as the Source step for HTML Code and Displayed Text:

The second step ‘Extracted Table From Html’ is similar to the step with the same name in Displayed Text:

For Upcoming Courses, the M code is more complicated, so let’s unpack it:

I looked at the syntax for Html.Table() last time:

This means that for this example, I have the specified CSS selectors columnNameSelectorPairs:

I also have options specified:

This step has extracted the information from the section of the html code that defines the table:

Since the query is already called Upcoming Courses, there has already been some extracting before this point to recognise that a table is being extracted under this heading information:

We can see that <tr>, <td> and <th> appear frequently in the html section.  <tr> indicates the table row containers, which contain the table data cells <td> and table header cells <th>.  The RowSelector  in the M code indicates that the function should be extracting the rows of TABLE.table, which is the table identified in this query.

The columnNameSelectorsPairs is extracting the html data into six [6] columns.  This also implies that some processing has occurred prior to the steps in this query to determine how many columns there are.  The algorithms also determine how the data is accessed: in the original table on the webpage, we may access a URL via a hyperlink to see more data about the course, but that URL is not extracted here.

We were able to choose to include the hyperlink destination URL when we created a custom table Table 1 in Part 1, so this could have been included if the algorithms were programmed to do so.

The final step of the Upcoming Courses query simply detects the data type for each column.  Next time, I will compare the code for this query with the tables I created using ‘Add Table Using Examples’.

Come back next time for more ways to use Power Query!