I am in a lot of meetings. A LOT of meetings. Double, triple, quad booked. I guess when you get to manager or director level somewhere, that is the definition of “busy”, or maybe everyone just wants you in their meeting, or your opinion, or whatever. In the end “Meetings are Toxic” (from 37signals), but really the are sometimes a necessary evil.
Anyways, do you really know where you spend all your time? Well you can glean the information pretty easily using Excel and Microsoft Power BI (Power Query specifically).
First, the key for me is to “categorize” my meetings. You can create categories in Outlook and then assign them to meetings, you can even color code the categories.
Where does Power Query fit in? Well, you can connect to Exchange as a data source.
Then you can query your calendar “table”, and pull it into Excel.
Then, as with any table, you can Pivot it, and pull over category as the row, and look at the count. With some column work in the Power Query query, you can split out the date/time and get Month/Day/Year and create a semi-hierarchy, to see things over time.
For example, I took over 2 teams in January, and my meetings with them and related projects skyrocketed in January. Now I know what was taking my time up for Q1 2014 🙂
At least the number is going down 🙂
There is so much more you can do with Power BI and Exchange data, your email, calendars, contacts, etc, this is just the tip of the iceberg, and it should only take you 10 minutes or so to get to this result! Now, if I can just figure out how to get out of the meetings!
4 replies on “Using Power Query to Analyze Your Schedule”
So are able to track both productivity and profit from this program or just track your time spent in meetings?
you can track the count and the time if you wanted to. You would have to somehow put a dollar value on meetings. If you equate your time to money you could add it up that way. In the details of the query it has people on the invite so you could probably just generalize a rate per person.
Steve, you are my hero. Thank you so much for this post. I am now doing my Timesheet in Outlook because of it.
Because I don’t have the “pro” version of Excel, I don’t have the Exchange option on Power Query, but I can use Power BI to accomplish the same thing. Also, Power BI Desktop just yesterday added the ability to export the data in a tile to CSV. So, I can now export my timesheet descriptions and hours summarized by day into CSV which allows me to copy/paste them into my timesheet program. I can also analyze my projected utilization rate throughout the week.
This is really slick and saved me a lot of time. No more disconnected timesheet program! I never would have thought to do this without your post. Thank you so very much!
Here is a write-up of what I did, taking your method and applying it to a timesheet, including analyzing billable hours: http://www.timrodman.com/a-power-bi-timesheet/