English monarchs and data...

This weekend's coronation of King Charles III was a dazzling event which has been a completely new experience for most people I know. The modern world offering high definition video across a head-spinning variety of devices and social media commentary remarking in real time on every little detail, connected with the monarchical tradition spanning centuries and including anachronisms such as the Stone of Destiny.

That gave me the idea to put together an English and British Monarchs data story in the form of a Power BI report. This allowed me to both practise further my technical skills and see what insights are hiding in at least some of the publicly available data on the nearly 1200 years of collective reigns of English/British queens and kings.

Several key insights stood out for me including the following:

• Queen Elizabeth II, who was the longest reigning monarch, was at the throne (70.58 years) more than four times the average reign (16.72) for all monarchs.

• The Wessex/Anglo-Saxon dynasties had the highest number of kings (no queens, unfortunately) - 16, but were the second longest reigning House with 175 years in total. It's not surprising that they didn't have a great average reign duration as they were the earliest monarchs of England, and the monarchy was less settled.

• The longest ruling dynasty was Hanover which lasted 186 years in power mainly thanks to Queen Victoria with her 63.59 years at the throne. The current house Windsor at 122 years still have a way to go before they can challenge at the top. Windsor are fourth longest though with a massive contribution by Queen Elizabeth II.

• The average reign has had a positive trend increasing by more than 10 years between 827 and 2022.

You can interactively explore the report in several ways as follows:

1. Link to Power BI.com

2. An embedded version below

3. A video recording here.

A couple of tips for the best experience as follows:

• Download, unzip, install font Raleway & restart your browser.

• Use the Full Screen version (button at bottom right of the below frame).

• Use the Zoom slider below to get the view which fits your screen size.

A few comments on the data and the report development approach as follows:

1. The core data set was sourced from Encyclopaedia Britannica, and I downloaded it from Statista.com. It is comprehensive as it even includes disputed monarchs such as Lady Jane Grey.

2. I loaded the data in a fairly simple data model (see below screenshot) consisting of a Reign_Data table where the grain was the English & British Monarch name field, and a Dynasty table for data at the House/Dynasty level.

3. I enriched the Statista data table with URLs pointing to images showing what each queen and/or king looked like. I hosted the images in OneDrive from where I was able to generate the URLs using the "Embed" functionality. More details on the approach in this article by Microsoft.

   1. Important to note that generating a link using the "Share..." functionality does not work for this purpose. Something I learned the hard way...

   2. Finding the right images storing them in OneDrive and especially getting the Embed links was probably the most laborious part of building this demo.

   3. I used the same approach to get images for the crests/coat of arms for each of the dynasties.

4. When creating the user interface of the report I took inspiration from the blog of one of the globally renowned Power BI data visualisation innovators - Kerry Kolosko. Her approach was invaluable as it gave me a really efficient way to put together an acceptable theme and landing page for a report like this one.

5. I did put together a JSON file with a theme as I wanted to use the official Royal.UK font. This post gave me the structure for the parameters to be added, and I also used the same file to store the Hex values of the main colours for the theme.

6. To complete the story I included the Wikipedia links for each of the monarchs to the data set and enabled navigating to those as an action in the Image visual.

I showed the report to my 10 year old daughter who found it very interesting to look at how the Crown was being handed down through the ages. I hope you find playing with this Power BI report just as interesting...