Power Query: Blog List – Part 1

Welcome to our Power Query blog.   I would like to get a list of all the Power Query blogs I have posted.  Today, I begin by extracting data from the first page.

I started writing this blog series way back in 2016:

There have been many developments since then, some of which I have already reported upon.  Since I am going to revisit some 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 am going to use Power Query in Power BI, since there are some functions that will help me to access web data, which are not yet recognised in Power Query (Get & Transform) in Excel.

I open a new Power BI desktop session and access the ‘Get Data’ dialog.  I then search for ‘web’:

The URL that I will need is from this page:

However, before I continue, I note that to get a full list, I am going to need several pages:

The first step, which I will cover this week, is to get one page.  To do this, I access the ‘From Web’ dialog and input the URL:

When I click ‘OK’, I am taken to the Navigator dialog:

Table1 contains the data I need, so I select that and click ‘Transform Data’. 

I will tidy up the data for this page.  I start by using ‘Choose Columns’ from the Home tab.

I would like to keep everything apart from Column4.  I would also like to sort the blogs in ascending date order:

I rename the columns to something more useful:

I choose to remove the beginning of the Description information.  To achieve this, I use the Extract dropdown from the Transform tab:

I would like to keep everything after the first comma (,).

This removes the repetitive information.

Now I am happy with this page of data.  Next time, I need to consider how to access the other pages. 

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