Power Query Calendar

A few weeks ago I wrote a blog post on how to create a graphical calendar using a PivotTable. I received a few questions about how to insert event names into the calendar. Generally, PivotTable value fields support numeric values (such as day 1, 2, 3), and not text values (such as Filing Deadline or Birthday Party). Fortunately, this task can be accomplished with Power Query.

Although I use an actual calendar application rather than Excel to keep my calendar, there are some cool PQ transformations involved in this post so hopefully these techniques will come in handy in other situations you may encounter.

Overview

We are trying to take a list of events stored in an Excel table, like this:

And pull them into a graphical calendar, like this:

And, of course we want to make it easy to switch months, so we’d like a slicer to control the calendar, like this:

There are several steps needed to get this working, but once it is working, the calendar dynamically adapts to your event list. So, a quick right-click and refresh will pull your updated event list into the calendar.

Note: the following steps assume that all events for a single date are included on the table row for the date.

Details

I’ve created a video and full narrative below for reference.

Video

Narrative

We’ll proceed with the following steps:

• Load Event List
• Create Calendar Query
• Display Calendar in Excel

Well, there’s no time like the present, so let’s get started.

Note: depending on your version of Excel, you may or may not have the same commands, screens, and options as my screenshots below which were created with Excel O365 for Windows.

Load Event List

First, we need to get the event list loaded into Power Query. To do this, we select any cell in the event table and click the Data > From Table/Range command.

The only transformation we need to perform in the resulting Power Query Editor window is to change the data type of the Date column to Date. One way to do this is to select the Date column and select Date from the Home > Data Type drop-down. The results should look a bit like this:

Also, to keep track of things, we’ll give this query a descriptive name, such as EventList, by typing it into the Name field.

At this point, we click the Close & Load To … command, and select Only Create Connection and clear the Add to data model checkbox, as shown below.

With this complete, it is time to move to our next step.

Create Calendar Query

Next, we need to create the query that generates the calendar. But before we do that, we’ll need to set up a few helper queries first. Specifically, we’ll need to create a query to compute the first day to be displayed, the last day to be displayed, and all the dates in between.

FirstDay Query

To create the FirstDay query, we right-click the EventList query from the Queries & Connections panel and select Reference. This creates a new query whose source is the EventList query. Then, we perform the following transformations:

1. Select the Date column and click Add Column > Date > Start of Month
2. Select the new Start of Month column and click Home > Data Type > Whole Number
3. Select the Start of Month column and click Transform > Statistics > Minimum
4. Name the query FirstDay

This should provide a date-serial number (not a formatted date), as shown below:

We Close & Load To … Only Create Connection, do not add to data model.

Next, we basically rinse-and-repeat to get the last date.

LastDay Query

1. Right-click EventList query and select Reference
2. Select Date column and click Add Column > End of Month
3. Select End of Month column and Home > Data Type > Whole Number
4. Select End of Month column and Transform > Statistics > Maximum
5. Name the query LastDay
6. Close & Load To … Only Create Connection, no data model

Now that we have the first and last days, we need to create a table that includes every day between them (inclusive).

DateRange Query

We’ll start with a blank query by selecting Data > Get Data > From Other Sources > Blank Query.

In the formula bar, we just enter the following formula …

={FirstDay .. LastDay}

… and hit the Enter key on our keyboard. Power Query generates a list of numbers starting at FirstDay and ending at LastDay.

Now, we just need a couple of additional transformations:

1. List Tools > Transform > To Table
2. Change data type to Date by clicking Home > Data Type > Date
3. Change column name by double-clicking the column label and typing the desired column label Date
4. Name the query DateRange

The results should look a bit like this:

Close & Load To … connection-only query, no data model.

With our helper queries complete, we can now create the query that generates the calendar.

Calendar Query

For this query, we use the Data > Get Data > Combine Queries > Merge command. In the resulting Merge dialog, we select DateRange in the first drop-down, EventList in the second drop-down, and then select the Date column from both as shown below.

To complete the next steps, you’ll need to be able to see the Formula Bar, so if you can’t see it, check the View > Formula Bar checkbox.

After clicking OK, we have a few transformations:

1. Expand EventList column by clicking the expand icon on the right side of the column, and clear all checkboxes except for Event
2. Select the Date column and Add Column > Date > Month > Start of Month
3. Select the Date column and Add Column > Date > Week > Week of Year
4. Select the Date column and Add Column > Date > Day > Name of Day
5. Select the Date column and Add Column > Date > Day > Day
6. First select the Day column, hold Ctrl, and then select the Event column. With both columns selected, Transform > Merge Columns. In the resulting dialog, select Space as the Separator and give the new column the name DayEvent
7. Look in the formula bar, and expand it so you can see all of the code for this step (you may need to click the down arrow on the right side of the formula bar). You want to replace the space delimiter ” ” with the line feed character “#(lf)”. So, change this Combiner.CombineTextByDelimiter(” “, QuoteStyle.None) to this: Combiner.CombineTextByDelimiter(“#(lf)”, QuoteStyle.None) and then hit Enter on your keyboard.

Before we continue, let’s be sure that our results are something like this:

Day names … if we don’t want the day names fully spelled out, and prefer the three letter abbreviation, we can select the Day Name column and select Transform > Extract > First Characters, and then enter 3 into the resulting dialog.

Next, we delete the Date column which isn’t needed going forward, so, we just select the Date column and Delete / Remove Column.

Pivot: The next transformation is a Pivot, and to perform this, we select the Day Name column and click Transform > Pivot Column. In the resulting dialog, we select DayEvent as the Values Column and expand the Advanced Options. We select Minimum, as shown below.

Next, we can just click-and-drag to adjust the column order as desired, such as Sun, Mon, Tues, and so on.

Finally, we just remove any columns we don’t want to send to Excel, such as the Week of Year column by selecting the column and hitting the Delete key on our keyboard.

We Close & Load To … Table, on a new or existing worksheet. Now, we just need to clean up the way we display the calendar in Excel.

Display Calendar in Excel

At this point, the calendar is displayed in Excel, like this:

Here are a few formatting things we can optionally perform if desired:

1. Top Align by selecting the entire table and clicking the Home > Top Align command (Alignment group)
2. Wrap text by selecting the entire table and clicking the Home > Wrap Text command (Alignment group)
3. Setting uniform column width for all columns
4. Tell the table not to change the column widths by clicking Table Tools > Design > Properties command, and unchecking the Adjust column widths checkbox
5. Add a Slicer to quickly select months by clicking Table Tools > Design > Insert Slicer and picking Start of Month.
6. Hide the Start of Month worksheet column (right-click column label and Hide)
7. Select from a variety of formatting options, including table styles and banding from the Table Tools design tab.

Well, there were many steps, but we did it!

And now, you can easily use the slicer to filter the calendar table for the desired month.

Plus, if you add events to the events table or want to update the height of the rows, just select any cell in the calendar table and right-click and Refresh.

Check out the sample file for a fully functioning workbook: PQCal.xlsx

If you have any other Power Query tips, please share by posting a comment below.