Excel Community project - rescuing the lost archive

In October 2020, the web platform used for Tech News and the Excel Community was changed. Although there are many clear advantages of the new platform, one downside is the splitting of the content for both communities between current articles and the very extensive archive of articles built up over many years. The faculty team have sought to alleviate the problems by providing a tabular index to over 2,000 archive articles.

It is this index that forms the basis of our project. Given that we are the Excel Community, it seemed only right to consider the use of Excel as a method of making our archive of articles far more useful to our members. Excel and Power Query do have a part to play, but the majority of the project will be based on the freely available Excel spin-off product: Power BI Desktop. If you haven't already explored Power BI, you can find out more about it, and download the desktop edition for free here:

Power Bi Desktop

Our plan is to combine the separate index pages for Tech News and the Excel Community to create a comprehensive table of links to the underlying articles. Because the archive is now fixed, we won't be attempting to create any dynamic link to the index pages on the Internet but instead we will just copy the index tables and paste them into an Excel workbook. This also makes it possible to add columns to the original tables to hold information that will make the archive more accessible and more useful: for example, keywords and summaries to facilitate searching the archive.

We will be making the various project stages accessible as we go, meaning that you can add your own information or categorisations.

Stage 1 – from web page to Excel Table

We will start by setting up an Excel workbook with two sheets ready to paste in the index tables from the Tech News archive:

Tech News archive

As well as the Excel Community archive:

Excel Community archive

We can just select the top-left cell of the index table and use Control+Shift+End to select the whole table and then use copy, before pasting each copy from cell A1 of each of our worksheets in turn.

Given that the archive tables are currently fixed, we could just paste one below the other to create a single table for subsequent use with Power BI but, in order to be able to cover a range of useful features and to provide for more flexibility, we will use Power Query to append our tables. We can just right-click in each table and choose Get Data from Table/Range but, so that we can easily give each Table a meaningful name, we will click on any cell in the table and use the Control+t keyboard shortcut to convert each of our ranges into a structured Excel Table first. We will call our Tables tblTechNews and tblEC. Using the tbl prefix can be helpful in identifying Tables in a list of named objects, for example as part of an AutoComplete list when creating a formula.

We can then right-click in each Table and choose Get Data from Table/Range to load it into the Power Query editor. For the moment, we will just use Power Query to append our two Tables so, for each Table, we will use Home Ribbon tab, Close group, Close & Load dropdown, Close & Load To… to just create a connection to our Table using the Only Create Connection option:

With our two queries created, we can combine them into a single output Table using the Data Ribbon Tab, Get & Transform Data group, Get Data dropdown, Combine Queries, Append option:

For the moment, we will just use Close & Load to load the resulting Table to a new worksheet with the default name Append1. For consistency, we will change the name of the Table itself to tblAppend.

We need to save our workbook once this Table has been created.

To finish this first stage, we'll just open Power BI and use Get data to select Excel, and then click on Connect to browse to our Excel workbook:

We should now see a list of Tables, Range Names and queries in our Excel workbook and the benefit of the tbl prefix should be clearer. We want to select tblAppend and then, for now, just click on Load to load our data into Power BI:

Power BI Report view should appear with our Excel Table columns listed in the Fields area. We can just click on Post to add a list of our posts to our Report:

Next time, we will add live hyperlinks to access our posts and start designing a dashboard to turn our alphabetical list of posts into an indispensable Excel resource. In the meantime, this is the link to the ongoing project:

Excel Community project

The Excel file is also available here:

Excel archive file

At the moment, it is still very much a work in progress and any comments on useful ways in which we could provide access to the community content would be very welcome:

excel@icaew.com