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 sample file

How to create a Dynamic Calendar Table

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!

Related Articles

Responses

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

  1. I’m not a fan of your use of Number.From, since it creates an abstraction issue. That is, in order to understand the subtraction, you need to know that dates will convert to a number of days, even though, intuitively, a date should not be able to be converted to a number. Below is a snippet of a “let” statement that I use to generate a table of dates. It does not have any abstraction issue.

    #”Start Date” = #date(2016, 1, 1), //Inclusive
    #”End Date” = Date.From(DateTime.LocalNow()), //Exclusive
    #”Date Table” =
    Table.FromList(
    List.Dates(
    #”Start Date”,
    Duration.Days(#”End Date” – #”Start Date”),
    #duration(1, 0, 0, 0)
    ),
    Splitter.SplitByNothing(),
    {“Date”}
    )

    1. Nice!
      That’s the thing that I love about Power Query. You can create solutions in many ways and all of them work without issues. Also, what’s the abstraction issue?

      Usually Ken uses the contiguous list operator where he transforms the date into numbers and creates a list {StartDate…EndDate} and then converts them back to dates, but we wrote these articles around 5 years ago(¿? give or take) and the easiest way for us to explain to new users what was going on was to show a simple subtraction as that’s what most people from Excel now, where a subtracting dates yields a number (of days). It also opened a lot of conversations about floating points and how things are stored in Excel vs how they are read in Power Query.

      In Excel it’s fairly simply to input a formula like [Date]+ [Number of Days], but this expression is not possible in the M language. It’s also fairly easy to do [Start Date] – [End Date] in Excel, but that exact expression doesn’t really work directly in the M language.

      All of these articles were written when Power Query was only part of Excel, so they always have the Excel user in mind and with the Number.From we try to convey the idea that things are not exactly the way that they are in Excel and that data types are super important in Power Query.

      Nowadays I’m using more Duration.From(1) than #duration(1,0,0,0) because of the whole intellisense (which I’m loving it), but we ended up realizing that the advantages in performance between all of these approaches was negligilble. They were all calculating in either a second or less, so it was more as to what was your personal preference or which option you’re more of a fan of.

      Nevertheless, I’m still wondering as to what abstraction issue you found. Would love to get more information about it so I can replicate. Perhaps it might even be a bug?

    2. ah! totally forgot to mention, but did you know that you can also do Number.From(#”End Date” – #”Start Date”)? It’s pretty cool!

      I believe that was the initial code that I shared with Ken, but we later decided to explicitly convert each value to a number so it’s easier to understand what was going on, but either one would work.

      But yeah! please share your findings about the abstraction issue

      1. I’m using the term “abstraction” in the computer science sense. Basically, the fewer details you need to know about something, the more abstracted that thing is, which is a good thing because we like stuff to be simple. Maybe I’m not using the word in the best way, and maybe I should use “clarity” instead.
        https://en.wikipedia.org/wiki/Abstraction_(computer_science)

        In Duration.Days(#”End Date” – #”Start Date”), we subtract 2 dates, and the result is, intuitively, a duration. Then we feed it to Duration.Days to get the number of days, and everything makes sense and is easy to follow. Similarly, it is clear in #duration(1, 0, 0, 0) that we are trying to define a duration of 1 day.

        You guys mention in the course that Power Query is strict on typing, and that it doesn’t make sense to add 1 to a date. 1 what? Day? Week? Similarly, the meaning of Duration.From(1) is not clear (duration of 1 what?) and you have to know that numbers converted to durations will be interpreted as a number of days. Similarly, using Number.From on a date intuitively shouldn’t make any sense, but Power Query will convert the date to its serial number, which is the number of days since a certain date. The expressions that you guys use do work, but they are hard to follow.

        A simpler example of an abstraction issue is converting the Excel formula A1-IF(B10,1,0) to A1-IF(B1,1,0) or A1-(B10). A1-IF(B1,1,0) requires that we know that IF interprets non-zero numbers as TRUE and 0 as FALSE. A1-(B10) requires that we know that TRUE converts to 1 and that FALSE converts to 0 when they are used in subtraction. The original formula is longer and a bit slower but is much clearer, less “hacky”, and probably more futureproof.

        1. I’m a bit lost on this one, Kenneth and pardon my ignorance, but I’m not clear on the message that you’re trying to convey.
          Most of the Excel users that I’ve personally met do the operations that I’ve commented. Even the Excel Pros and MVPs that I’ve met do follow the same.
          They add numbers to dates as if they were days, so intuitively to them when you talk about adding a “Number” to a date in Excel, they immediately think that you’re adding a day. So we tried to made it as comparable to an Excel operation to them as possible.
          The term “duration” has always been one of those “too technical” terms for most Excel users, so we intentionally tried to avoid anything that might go over people’s heads in these articles.
          In the end it’s all about having the right message to the right audience, but if you’re coming from a CS background, then you can absolutely get into the details and take advantage of the concepts that might be a bit too complex for the average user.

          1. My argument is that we should do what makes sense. Outside of Excel, dates do not have numeric values and it doesn’t make sense to add numbers to dates. When you subtract 2 dates, it does make sense that this difference represents a length of time (i.e. a duration). Your code tries to simulate the illogical Excel way to appeal to the Excel people, but to everyone else, your code is difficult to understand. My code is easier to understand by those that are not stuck in the Excel mentality.

            I don’t advocate doing things the illogical Excel way in Power Query just because that is what people are used to and their way still works. It’s like if I call Native North Americans “Indians”. That is what people have been calling them for years, and people today still know what I’m talking about, but it is still incorrect and will not make sense to anyone that is not North American. Saying “Native North American” is overall clearer but may trip up those that are used to saying and hearing “Indian”.

            It is fine to use the Excel way as bait, but I feel that it is also important to show better code so that people know what good Power Quer code looks like and so that they stop looking for similarities to Excel, which won’t always exist. In fact, Power Query barely resembles Excel, and as they say for learning a new (written/spoken) language, it is better to learn it directly rather than continuously translate back and forth to a language that you already know.

            1. I understand where you’re coming from and, again, it’s a matter of preference and perspectives in the end.
              Different viewpoints will require different approaches for the topic to make sense.
              We took the approach that makes the most sense for the average business user that works with Excel. If you spoke to me in English around 12 years ago, I wouldn’t have understood you that well (maybe around 70% at best), but if you were speaking to me in my native language (Spanish) I would’ve absolutely understood every word that you said.
              Something similar happens here – we’re trying to speak in the same “language” that the Excel users speak, mainly because the tool was created for business users and it was only available within Excel back then.
              We do value your feedback! The good thing is that these pieces of code are now and the comments section and we might even add the duration techniques as a side note within the article.
              All codes mentioned here work and provide the result that we require – there’s negligible performance difference between each other, so in practice there’s no “best” way to accomplish the result as it depends on the user to understand what makes more sense to him/her/they.

              Also, I just want to strongly disagree on the “bait” part. We do not show the Excel code as “bait” nor do we try to “bait” anyone with any piece of content. We understand where you’re coming from, but please bare in mind that this is also content created by 2 Excel / Business users that tried to pass some content to other Excel / Business users.

              If you’d like to learn more techniques about calendar tables, I do have some draft code available on GitHub to create specific types of Calendar tables which might be of your interest if you’re looking for some specific approaches when creating calendar tables:
              https://github.com/migueesc123/CalendarCreator4PowerBI/blob/master/CalendarCreator/CalendarCreator.pq

        2. My reply used “greater than” and “less than” signs that are not appearing (all of the “B10” that you see were supposed to be the Excel way of saying “B1 ≠ 0”). What is going on? Are replies meant to accept HTML code? Are certain characters purposely removed before a reply is posted? Are these replies safe from cross-site scripting?

          1. Hey!
            The comment section doesn’t accept rich text. Any type of html code shall be stripped when hitting the “Publish” button. For security reasons, cross-site scripting or any form of sql-injection shouldn’t be possible. If you happen to find any instances of this, please let us know!

            1. Can the characters be escaped as opposed to removed? I’m probably not the only one that will end up posting “greater than” or “less than” as part of code.

              1. thanks for the idea! We’re actually using the responses from the feedback widget (available on the right hand side of the screen) to drive changes within our systems.
                I’ll add this to our list and continue to monitor the feedback.

Magic-Tricks-for-Data-Wizards-1.png

ARE YOU IN?

15K+ subscribers have already claimed their free eBook.

Sign up and get your FREE copy