Dynamic Calendar Table

The Scenario

 

When you are facing a scenario where you need to create a Dynamic Calendar Table (or Date Dimension Table), we take in consideration that we could create such table in Excel and use it later in our Time Intelligence Scenarios with Power Query or Power Pivot.

Most of the time, this Date dimension would be too long and would contain dates that are not relevant to the current report. (Like dates from the year 2020 for example and we only have data until the year 2015; that translates into 5 years of emptyness or waste in our report)

There are other scenarios where you simply want a nice calendar table in with text labels or other formats using your specific culture (Spanish, Portuguese & such)

This Pattern will try to show you how to create a Calendar Table, how to use some of the Date functions found in Excel, and how easy it is to work with Power Query.

Download the Workbook

Step 1: Create the query and Add a Start Date

Before we begin adding any type of code, we need to create the query. For that, we’ll go to Power Query ->  Other Sources -> Blank Query.

That will launch the Power Query Editor and within that window we can add our own code in the formula bar. Inside that bar we add the following code:
=#date(2013,1,1)

DateLiteral

You’ll notice that we just added something called a literal by using #date(2013,1,1). What we did is simple – we added a date value. 2013 is the year value, the second parameter is for the month and the last parameter is for the day.

We added that in our first step. The name of that step is Source and it holds the value of the start date of our calendar table.

Step 2: The code that does the Magic

Let’s go ahead and create a new custom step by clicking on the fX icon right in the formula bar. This will add a new step called Custom which would reference to the previous step. We would like to change that original code in the formula bar to be this one:
= List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source) ,#duration(1,0,0,0))  

Here’s the breakdown of that formula:

  • List.Dates – it helps us a create a list of dates and its inputs are
    • A Start Date
    • The number of values to return
    • The increment to add
  • Source – as you already know, this is the value of our Start Date so it goes inside the first parameter
  • Number.From – we use this function multiple times to transform any type of value into a Number and use it for math operations
  • DateTime.LocalNow – this is the equivalent of NOW() in Power Query so it returns the current date and time. This will make sure that we only get a dynamic range of dates until the present day
  • #duration(1,0,0,0) – another literal that adds 1 day (so that our increment is on a daily basis)

This formula returns a List of Dates as you can see in the next image

FormulaMagic

 

Go Ahead and convert that list into a table using the List Tools ribbon. After you convert it into a table, please change the data type of that column to be Date and rename that column to something more descriptive like Date.

Step 3: Adding the date/number-based columns (using the UI)

Next step is to add the much needed columns such as Month Number, Year, Start of the Month Date, End of the Month Date and all that good stuff.

We do that by simply following these steps (please make sure that you select the Date Column):

  1. Go to the Add Column ribbon
  2. Go to the From Date & Time Group
  3. Select the new column that you want from the dropdown

Here’s how the result would look like:

DateUI

Step 4: The text-based columns (and the culture option)

This is the last step and we’ll add some new custom columns by going to Add Column -> Add Custom Column.

Next, we’ll add these new columns by inputting the formulas to the Add Custom Column window:
Month Label Column = Date.ToText([Date], "MMM", "es-419")
WeekDay Label Column = Date.ToText([Date], "ddd", "es-419")

In that last parameter we used the text value “es-419” so we can get the labels in Spanish.

You can use the following link to see the National Language Support (NLS) API Reference and find other cultures. This last parameter is optional and if you don’t use it then all will be calculated using the English (en-US) language.

The Final Result of this could look like this:

FinalTable

Conclusion

We now know how to create a simple Dynamic Date Dimension or Calendar table and how the UI works in order to create some new date columns.

This truly shows how extensible and easy to use Power Query is.

Show us your version of your Power Query calendar tables in the comments!

Power Query Patterns
Power Query Book
Power Query Workshop

this work was shared 0 times
 600
About

 Power Query Training

  (13 articles)

We are a team committed to delivering the best content for new and seasoned users of a tool that we believe in: Power Query. We firmly believe that this is THE best tool to be added to Microsoft Excel in a long time.

  • Dominik Petri

    Hi Ken & Miguel,

    In a comment to my post on Rob Collie’s site (http://www.powerpivotpro.com/2014/02/the-ultimate-date-tablerevisited/#comment-12916), Marco Russo mentioned to use dates until December 31. Therefore, I use this to in my M-code:
    EndDate = Date.EndOfYear(Date.From(DateTime.LocalNow()))
    Looking forward to your book!
    Best regards from Germany
    Dominik.

    • Thanks, Dominik!

    • drifto

      Hi Dominik, this sounds really interesting. Could you explain for a beginner where this edit was exactly performed?

      • By using DateTime.LocalNow(), your calendar will end today. To have the calendar end on December 31, wrap the function in Date.EndOfYear()
        The formula in the step Custom should then read
        = List.Dates(Source, Number.From(Date.EndOfYear(DateTime.LocalNow()))-Number.From(Source), #duration(1,0,0,0))

        • drifto

          Great, thanks! I had done cut and paste stuff but my version missed the last day of the year. 😀
          = List.Dates(Source, Number.From(Date.EndOfYear(Date.From(DateTime.LocalNow()))) – Number.From(Source) ,#duration(1,0,0,0))

          • You don’t need the Date.From() function. Date.EndOfYear(DateTime.LocalNow()) will be converted into an Number by Number.From(). If you omit the Date.From() like I did in my previous post, the calendar ends on Dec 31

  • Daniel

    Guy’s,
    I am getting an “conversion into number impossible” error (loosly translated from German) in Step 2 for the #date(2015,10,1)
    Any ideas why?

  • Lucas Sanchez

    Thank you guys very useful. Is still necesary to use Calendar Table with New Time Intelligence features in Power BI: https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-2-6a-explore-time-based-data. If yes, which are the benefits? Thank you in advance.

    • Ken Puls, FCPA, FCMA

      I guess it depends on two things: 1) if the formats they provide are sufficient for your needs and 2) does your calendar follow a standard 12 month calendar with a Jan 1 start date. (Of course this calendar runs similar to the new built in functionality, but that doesn’t mean that all will.

      Also, remember that if you’re using Excel, you don’t have the built in functionality with Power Pivot.

      Personally, I prefer to build my own calendar tables just so I have explicit control over them. But that may be just me.

  • Pingback: Create Dynamic Calendar Table or Date Dimension...()

  • Pingback: How To Create A Training Calendar | Rubyjane()

  • Pingback: Power Query is awesome – dynamic table of dates – #powerbi | Erik Svensen()

  • Benjamin Harder

    Very good article. Just one suggestion – when copying your example ” =#date(2013,1,1)” into the query editor, there is a leading empty space, which causes the query not to run until removed.

    As i’m a beginner, that costed me quite some time to figure out – maybe you could point this out in your tutorial, so others don’t step into the same pitfall… 🙂

    Kind regards,
    Benjamin

    • Hey Benjamin,

      Thanks for the input! We went ahead and made the changes so they don’t have the leading whitespace. Sorry about that!

x