Power Query: Blog List – Part 1
29 November 2023
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image1-1699869905.png/ced77e8a8df2b723cf4c2e9dd1d1cb53.jpg)
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’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1699869947.png/dbc62832919f292e3850ed4232584b89.jpg)
The URL that I will need is from this page:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1699869970.png/3f2608719226b9491fed619b5bf11673.jpg)
However, before I continue, I note that to get a full list, I am going to need several pages:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1699869989.png/027697f26b8709fd11e713e93bf7ac94.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1699870007.png/355562b96d1cb77dc8a88969480e8f75.jpg)
When I click ‘OK’, I am taken to the Navigator dialog:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1699870035.png/f8b38141db7df4a2f95192a02f83f8fc.jpg)
Table1 contains the data I need, so I select that and click ‘Transform Data’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1699870069.png/cc43c80e07f8f378649927f009c06864.jpg)
I will tidy up the data for this page. I start by using ‘Choose Columns’ from the Home tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1699870091.png/61e88f37c952e826f25b0faa26c377fb.jpg)
I would like to keep everything apart from Column4. I would also like to sort the blogs in ascending date order:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1699870116.png/41a2cd1f1d291a635533d14367a07895.jpg)
I rename the columns to something more useful:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1699870140.png/90d5ca71ff9021b7fc93c2b67bc3f69b.jpg)
I choose to remove the beginning of the Description information. To achieve this, I use the Extract dropdown from the Transform tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1699870160.png/51e91d0960b1b01050b122a6fe7c060c.jpg)
I would like to keep everything after the first comma (,).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1699870176.png/57488f4e1a50e7e31d76f8a754aa1199.jpg)
This removes the repetitive information.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1699870195.png/4597580c5ac77c3c0cdbf2b3dbbcea6d.jpg)
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!