Build Your Own Mondrian Dashboards

Recently we published a couple of new Mondrian Dashboards which leverage Excel’s latest Power Query and Power Pivot technologies. The results are excellent when combined with Oxford Economics Excel Data Workstation.

image

In this post, we’re going to show you how do build these Mondrian Dashboards yourself.

Below is a graphic with 9 tried and tested steps which you can follow to build your own flexible interactive dashboards in Excel using Power Query and Power Pivot!

image

 

Nine Steps Recipe to Your Own Mondrian Dashboards

1
  • Decide upfront which indicators (variables + locations) you want to show on your dashboard as either charts or tables.
  • Create a new Excel worksheet and name the tab appropriately.

2
  • Open Excel Data Workstation (EDW), aka Excel Plugin, by clicking on the Oxford Economics ribbon menu.
  • Using the Data Series selection tool, pick your indicators and choose your period, frequency and measurement settings.

3
  • Using EDW’s Add Data Series command, download the selection into Excel.
  • Recommended, but optional, convert the worksheet to a data table (CTRL-T) and name it appropriately in Design | Properties | Table Name.

4
  • Using Excel’s Power Query ribbon (enabled via COM Add-Ins) create a data query on the worksheet via Power Query | Excel Data | From Table command.
  • This opens the Power Query editor application with a query of the same name as your data table.

5
  • In the Power Query editor, use the Transform | Any Column | Unpivot Columns ribbon command to convert the several horizontal year data columns into two columns.
  • They will be automatically named Attribute and Value.

6
  • Rename the unpivoted columns (Attribute, Value) as you desire (e.g. Year, Data).
  • Notice the type of the Value/Data column is automatically set to Decimal Number.
  • If you have YYYY QN Year formats, use Power Query’s Transform | Split Column command to create separate Year and Quarter columns.

7
  • Remove any columns you don’t require using Manage Columns | Remove Columns command.
  • Notice the individual query commands being applied are recorded in the right hand panel. They can be edited or removed allowing safe experimentation.

8
  • Click Close | Close & Load | Close & Load To…
  • In the pop up dialog box select Only Create Connection and check Add this data to the Data Model.
  • Click the Load button.

9
  • Open the Insert menu. Click Charts | Pivot Chart | Pivot Chart (or Pivot Table).
  • In the dialog box, choose Use an external data source, and click Choose Connection to select your newly-created (power) query.
  • Check New Worksheet and then click OK.
    Configure the pivot chart (or table).
  • Repeat Step 9 as many times as required

 

If you download any one of the new Mondrian Dashboards you can examine the VBA code we wrote to automate data updates using Oxford Economics Excel Data Workstation. Feel free to reuse and modify as much of that code as you like.

Have fun and let us know how you get on!

 

Arvindra Sehmi is Chief Information Officer and Director of IT at Oxford Economics.