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.


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.


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



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.


Posted in , ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.


  1. Deirdre Leigh on February 11, 2019 at 4:14 am

    I was really excited to have a go at this but excel 2010 doesn’t have the Day Name option so i struggled to continue.

  2. Krista on February 11, 2019 at 1:39 pm

    Thank you so much for posting this! I do have a question though, is there a way to change my query so that it will list more than one event per day? It seems like it is only grabbing the first event. Unless I did something wrong? Thanks!

    • Paul on January 25, 2020 at 5:47 pm

      The way I do this is by pressing ALT>ENTER, that way I can list different events but they are still contained in the same cell. Hope this helps.

      • Hanie on September 12, 2022 at 2:28 pm

        Is there another way to do this besides ALt enter? My list of events is being pulled by a formula so I can’t use ALT Enter 🙁 Please help!

        • Maria on November 17, 2023 at 1:37 pm

          I have the same question. I’m not seeing how to do Alt Enter and see the multiple items . Please advise. thanks.

  3. Ian Cramp on February 18, 2019 at 4:51 am

    Using Office 365 I had a few issues, but got as far as the query for number of inclusive days, and cannot get past “Expression.Error: The name ‘FirstDay’ wasn’t recognized. Make sure it’s spelled correctly.”. Any clues?

  4. Paul on January 25, 2020 at 5:43 pm

    This. Is. Amazing.
    I think this is what I’m going to use to keep track of staff schedules!

  5. Dinah on February 15, 2021 at 3:46 pm

    I am also interested in doing this but my data is housed on separate rows, as they are different events entirely… Any advice on how you may include each row under one date would be greatly appreciated.

  6. Carola on March 1, 2021 at 7:14 am

    Hi, I got the calender to work, but I kept weeknumbers in mine, but the calender do not display the correct week number. Is there anyway of fixing this? I tried to find a way to change the query but without any luck. And also, I can add new events, and delete them, but when I try to filter them, they still show in the calender. Is there a solution to this?

  7. Andrew S. on January 25, 2023 at 2:57 pm

    I love this! The only issue I am having is, the calendar only displays one item per day; however, I need multiple things to be displayed on a single day. I cannot seem to figure out how to do it. If I could get a response back, I’d greatly appreciate it.

    Great videos, and top notch content. I have searched all over, and this is by far the best I have seen.

  8. Mieke on July 24, 2023 at 7:42 am

    Thank you very much for this it works perfectly. I am doing a birthday calendar for the office based off information from within a spreadsheet and this works perfectly except that we have multiple birthdays falling on the same date and I am battling to get these to reflect on the same date. It only shows one of the birthdays on the date. Still working on it. Again, thanks a lot for your informative videos 🙂 Mieke

    • Danielle on August 16, 2023 at 4:01 pm

      Did you find an answer to your issue of only one birthday populating if multiple birthdays are on the same day?

Leave a Comment