Power Query: A Tangled Web Part 1

Welcome to our Power Query blog.  This week, I use the ‘Add Table Using Examples’ functionality to create custom tables from web data.

When extracting data from the web version of Power Query, it 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 will use to investigate web data is from our very own website:

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

In a new Excel workbook, I access ‘From Web’ in the ‘Get & Transform Data’ section of the Data tab:

I enter the URL https://www.sumproduct.com/courses:

As I have accessed our website many times, the connection details exist for me already; I have used the default Anonymous credentials.  When I click OK, I may view the Navigator dialog.

I want to examine the way that the web connector automatically creates M code for the different options on the Navigator.  In addition to the data already available, I am going to create another table using the ‘Add Table Using Examples’ functionality. 

I have named the first column Course Name and started typing the first course name from the table on the webpage.  I have been prompted with some options from the page.  Note that not all the options are from the table.  For example, ‘Contact Us Today…’ is from lower in the page. 

I double-click on the course name in the dropdown and repeat the process for the second row.  The algorithm fills in the rest of the data for this column and I choose to add another column using the ‘+’ icon. 

Having added two [2] more columns from the table, I choose to add some data from elsewhere on the page.

Since this does not occur in the table, it is not filled down for me.  I also get a message when I press ENTER to accept the value:

Trying to add data outside the table I was looking at has given me an error:

‘No CCS selector was found for the sample values you provided in the following column(s): Contact’

It appears that I cannot combine data from inside a table and data outside of that table in the same custom table.  I will add the current custom table, and then add the training contact separately by using ‘Add Table Using Examples’ again:

I enter this data and click OK:

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

Next time, I will use ‘Transform Data’ to view the M code used to capture the data I have chosen.

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