Create a Planning View of Your Outlook Diary in Power BI - Excelerator BI

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.

image

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

 

image

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).

image

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.

image

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.

image

Power Query Online Training

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

image

And then I changed the column type to Integer.

image

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.

image

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).

image

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

Power BI Online Training

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.

22 thoughts on “Create a Planning View of Your Outlook Diary in Power BI”

  1. Hi Matt,

    I’ve a report where I want to grab emails from a specific subfolder in my Inbox and this is easy. However, when I click Close and Apply in the Query Editor, I can see that it takes too long to apply and that’s becuase I can see it going through all the mailbox subfolders not just the one I filtered on in the Query Editor. Any idea how to just limit it to only the subfolder I need?
    Thanks
    John

  2. Hi Matt,

    Can follow and create the calendar on my mail box but was wondering if you had been able to add other team members. I have shared calendars that I cannot get to connect to. Would really like a calendar that has all the team on it.

    Thanks
    George

    1. You could write a conditional formatting measure that checks for the category and use that. That is what I did for my version as follows. It returns black if there is more than 1 item

      Colour Code = VAR CurrDate = MAX(‘Calendar'[Date])
      VAR FilteredDiary = FILTER(Diary,Diary[Start] <= CurrDate && Diary[EndAdj] >=CurrDate)
      var Category = CALCULATE(SELECTEDVALUE(Diary[Categories]),FilteredDiary)
      return switch(Category,
      “Public Training”,”Green”,
      “PUG / Community”,”#E48BB5″,
      “Conference”,”#FFF100″,
      “Holiday”,”#C1E9FE”,
      “Public Holiday”,”#55ABE5″,
      “Remote Working”,”#FECB6F”,
      “Online Training”,”#F07D88″,
      “Black”)

  3. Thanks for the blog, i need create a this similar but whit the categories of the meeting in my Calendar. I try get the data but i can´t see the categories of the meeting. How can i do that?

    1. When I did it, there was a column called categories. It contained a list, and I was able to extract the categories from there

  4. Hi Matt,

    Thanks for the blog. I also made a similar Power BI report from Email Exchange.
    Now, I want to schedule refresh it online. I am facing issues in this since last week.
    Can I really set a scheduled refresh for Mail Outlook connection in Power BI?

    Many Thanks
    Naman Mittal
    BI Consultant
    Kockpit Analytics

    1. I don’t see any reason why not, as long at the mail server is available (visible) to PowerBI.com. This should be the case by default as long as your organisation is using Exchange Online. If this is the case, you will need to go into the dataset, scheduled refresh and find the connection to Exchange Online, and enter your username and password. Then it should work. If you are still using an on premise version of Exchange, you will need to have a gateway installed and then you will still need to configure that for your online exchange server.

      1. I tried both ways.
        In service, adding data source gives an error: “Failed to update source credentials. We’re sorry an error occurred during your evaluation.” so cant get passed that.

  5. Matheus Matsumoto

    Hi, I did the calendar and it worked perfectly. However, all my reincidents meetings (meeting I have every week for example) they did not showed up in the calendar. I could see just the first day of the scheduled several meetings.
    Do you know how to solve this and show my calendar the same as Outlook?

    Congratulations on the idea and website!

    Matheus

    1. So you mean you have a recurring appointment in your calendar, and only the first appointment appears in your diary? mmmm, interesting, I didn’t know that. My guess is you will need to deal with it yourself. Check if you and find the repeating information coming in from Outlook. I guess you will then need to materialise the future appointments yourself using Power Query. I am pretty sure this can be done, but not something I can look at for now.

  6. This is a great idea, i had not thought of linking to Excel. i have a vision: show a list of how many days each of my emails has sat in either the inbox or todo folders or whatever. i use my inbox as a todo storage, not good i know, but some items get away from me in terms of time. an aged listing of items greater than X days.

    1. I am also curious about fetching multiple users’ calendars, for using it with my team. Have you found out anything yet? 🙂

  7. I look for something similar, but with multiple (source) calendars from my entire team. Any idea if this would work?

  8. Mmm, that is not my area of expertise unfortunately. Did you try both of the connector types (exchange and exchange online)?

  9. Nice idea! But when tring to connect to my personal exchange account, I get the “Autodiscover service not runing” error. And after a few quick looks on the “resolutions” internet gives…. I do not want to spend a long time fixing this. (so yet another annoying hour, just trying to connect to data….for the 345 th time)

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top