Create a Planning View of Your Outlook Diary in Power BI

I was chatting to my wife the other day discussing all the things we want to do next year. It has long been a pain in the butt to get a high level (big picture) view of your Outlook diary to see what is happening (think forest, not trees).  In the past I have used some VBA in Outlook that produced an HTML year view (it was pretty good actually), and you can also create your own views in Outlook directly.  But I decided to use Power BI to try to solve this problem.

Load Data from Exchange

The first thing I did was to connect Power BI Desktop to my Exchange server.  There are 2 choices and it depends on your setup which you use.  I have my own domain and O365 configured to use Microsoft Exchange, so I connected to the second one below.

The Mailbox address is simply my email address as shown below.

For this scenario, I only connected to the Calendar, but you can connect to a number of other data objects too as seen below (that may be another blog one day).

I then edited the query to just keep the data I wanted.  I used “choose columns” to select just the columns I needed for my report (this is good practice).  In my case I only decided to load the “All Day” events as these are the “big picture” things I wanted to see in the year ahead.

If you do this yourself, you may choose to load Categories and use those to determine what to load, but in my case I am only loading all day events.

I then filtered to keep just the “All Day” events, then removed the IsAllDayEvent column.

Next I created a Duration column (Add Column\Custom Column)

And then I changed the column type to Integer.

And finally loaded the data.

Visualising the Big Picture

I looked into a few different visuals to visualise this data, and actually I couldn’t find one that did exactly what I wanted.  I looked at several Gantt Chart visuals and also most (if not all) of the calendar visuals.  The one I found closest to my needs was the Calendar by MAQ Software.  You can see it configured for this data below.

This visual allows a monthly view of the big picture.  I would actually prefer a scrolling full year view – I sent an email to MAQ software requesting this enhancement.  Maybe you can send an email too to help get that prioritised.  Just click here.

A Matrix to View the Data

Given the above month view was not quite what I was after, I created another view using a Matrix as shown below.  My idea is to have a single row per week with bars indicating when I have something happening (All Day events of course).

There are a few tricks in this one.  This is what I did.

• • I created a calendar table and added the following columns
    • Day Name
    • Day Number of week (starting from Sunday) for sorting purposes
    • Start Date of week.
  • I did not join the calendar table to the data table, but instead I wrote a measure that picks up the filter from the Matrix and pushes it onto the data table.  I needed to do it this way as there are 2 date columns in the events table.  Here is my measure.

Count of Events = 
     VAR CurrDate = MAX('Calendar'[Date])
     VAR FilteredDiary = 
            FILTER(Diary,Diary[Start] <= CurrDate && Diary[End] >=CurrDate)
     VAR TotalEvents = COUNTROWS(FilteredDiary)
     RETURN TotalEvents

• • Added a Matrix as shown above.
  • Added the Count of Events measure to the Matrix
  • Applied conditional formatting to the matrix (shown as 1 above)
    • Background colours the same for Min and Max
    • Do not apply formatting for blanks
    • Font colour the same as the background colour
    • The net result is the bar chart above.
  • I then created a Tool Tip (2 above) and configured it for the matrix.  I had to create a special measure (because there is no relationship).

Name of Events = 
     VAR CurrDate = MAX('Calendar'[Date])
     VAR FilteredDiary = 
              FILTER(Diary,Diary[Start] <= CurrDate && Diary[End] >=CurrDate)
     VAR Events = CONCATENATEX(FilteredDiary,Diary[Subject],", ")
     RETURN Events

Publish to PowerBI.com and access via Mobile

Once you have this template up and running, you could publish it to PowerBI.com and then access it from the mobile app where ever you are.

Why not Build this Yourself?

I provide remote coaching and if you want to learn the skills to build this template or any other templates that you need to do your job you can contact me.