Excel Community project – rescuing the lost archive – part 2

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, at:

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.

The story so far:

Excel archive file

Part 1 – from web page to Excel Table

Stage 2 – from Excel Table to web page

In part 1, we copied the tables containing the indices to the Tech News and Excel Community archive of announcements, forum posts and blogs into an Excel workbook. We then read each of the tables into Power Query so that we could then use the Data Ribbon Tab, Get & Transform Data group, Get Data dropdown, Combine Queries, Append option to combine them into a single table. We then used this combined table as the source for our Power BI report.

Unfortunately, when we read our Excel Table into Power Query, our hyperlink cells are converted to plain text cells that just contain the display text, rather than the actual hyperlink. This means that our project has, so far, succeeded in creating a single table but at the cost of losing the clickable links to the archive content that we want to access.

Restoring the hyperlinks isn't that straightforward. As far as I can work out, although the Excel HYPERLINK() function allows you to create a hyperlink in a cell with a URL and separate display text, there is no corresponding function to extract the URL element of an existing hyperlink. In order to address this, I resorted to a short piece of VBA code from Allen Wyatt, adapted to place the URL in column G of each row (an offset of 0 rows and 6 columns to the right of column A):

Extracting URLs from Hyperlinks

Sub ExtractHL()

Dim HL As Hyperlink

    For Each HL In ActiveSheet.Hyperlinks

        HL.Range.Offset(0, 6).Value = HL.Address

    Next

End Sub

The macro needs to be run once on each of our two worksheets. A version of the Excel workbook with the hyperlinks already extracted into column G is available here:

2021-01 Archive 02.xlsx

We now have our hyperlinks as text entries in cells, and we could use the HYPERLINK() function to turn these into active hyperlinks in Excel, but we will be using Power BI to access the hyperlinks, so we well leave them as text in our Excel Table.

As long as we have given both of our new columns the name Hyperlink, with the same capitalisation, we should just be able to refresh our tblAppend Table in the Excel workbook to include our new column:

Importantly, we then need to save the workbook before we go to Power BI as Power BI works with the last saved version of our file.

In Power BI, again we just need to use Home, Refresh to make our new, Hyperlink, column available:

Our column contains our URL, but it is not yet a clickable Hyperlink. To change this, we will switch to Data view using the middle icon to the left of our report area. We will see our data as a table, and we can then select our Hyperlink column and choose a Data category for it. We choose Web URL from the dropdown list:

Now, we switch back to Report view and select our existing, one-column, table and then click on the check box next to our Hyperlink field in the Fields pane. This adds a Hyperlink column to our table:

We can see from the formatting of our new column that it is no longer text, but now a clickable hyperlink that we can use to access our chosen article in a browser with just a click. Our hyperlink text is long and doesn't all fit in the space available, so we will click on the Format icon in the Visualizations pane, click on the Values dropdown and scroll down to turn on 'URL Icon':

This makes our Hyperlink column much more compact but still with the ability to load any article into a browser with a single click.

Next time, we will start to explore different ways in which we can use Power BI features to search through our archive.

This is the link to the ongoing project:

ICAEW spreadsheet knowledge base

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