Power Query: Blog List – Part 6

Welcome to our Power Query blog.   I would like to get a list of all the Power Query blogs I have posted.  Today, I welcome some new functionality, currently in Preview, which will allow me to achieve my goal in Power Query in Excel.

I started writing this blog series back in 2016:

There have been many developments since then, some of which I have reported upon.  Since I am going to revisit more of the areas that have been improved, I am going to start with a reminder of what we have covered so far.  In order to do this, I used Power Query in Power BI, since there are some functions that will help me to access web data, which were not yet recognised in Power Query in Excel.  However, this is changing, as I will show shortly.

In Part 1, I noted that to get a full list, I am going to need several pages:

I extracted and tidied up the data for the first page:

In Part 2, I changed the Source step of my query to accept a parameter P_PageNumber, which allowed me to get the data from a specific page.  I used the following M code:

I checked that this still gave the same results for the query.

In Part 3, I converted this query into a function, and generated a list of page numbers to concatenate my data.

In Part 4, I finished the query by extracting the last page number from the data and using it as a parameter P_LastPage when generating the list.

The list was then generated, and could be refreshed, no matter how many pages exist.

Last week, I looked at why I couldn’t do this in Power Query in Excel.  However, having completed my series on ‘Blog List’, I was delighted to find that the first blog series that I can update is this one, as there are some changes which are currently in Preview in Excel. 

Today, I am running a Beta version:

I use the web icon in the ‘Get & Transform Data’ section of the Data tab, and I enter the same URL that I used in Part 2 to get the first page of blogs.

When I click OK, I can see that the Navigator dialog has been improved, and now resembles the same dialog in Power BI, which I looked at in detail in Part 1:

I select Table 1 from the ‘Suggested Tables’ and transform the data:

If I look at the Source step, I can see that the function Web.BrowserContents() is now recognised.  

Similarly, if I look at the ‘Extracted Table From Html’ step, I can see that the function Html.Table() is also recognised.

These were the functions that I noted were missing last week in the Generally Available version of Power Query in Excel.

This means that the process I described in the ‘Blog List’ series of blogs can be performed in Power Query in Excel in the Beta version, and it will soon be Generally Available.  This is very good news when it comes to extracting data from the Web!

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