Demystifying Date Dimension Part 3

Hello All,


Let us take a deep dive into another way of creating the Date Table. However, for the next few series of blog posts, we will focus on M Query and we will first work with static values and then modify the M Query to make it more dynamic.

Steps that you can follow along with me are as follows: 

We are using the sample data from the previous post. You can download the sample file and have a look at the first 2 parts of this series here and here.

Click on the edit queries/transform data.

Click on the new source and click on a blank query.

This should look something like this.

In the formula bar type in = List.Dates(#date(2020,1,1), 365, #duration(1,0,0,0)) and press enter.

This should have given you something like this.

I hope that you recall from previous posts the three main components that I spoke about, StartDate, EndDate, and the Duration. Once again I will reiterate that this is all that we need for any date table. How we get this is all about manipulating the code, but eventually, that is all we need to create a date table.

Based on this let us break this formula and understand each component of it.

  • The List.Date function takes in three parameters, start as date, count as the number, and duration as the increment.
  • The start is the beginning date or the date when my calendar should start. In this example, I am using the beginning of this year as the start.
  • The count is the number or how many such date instances do you want to have on this list. I am choosing 365 as my count. Be careful that this count is the number of days. It is for several instances. If in the duration we choose hours, then this instance will mean 365 such hour instance.
  • The step is the last parameter, it simply means what is the duration or increment of each instance. I am calling the function #duration and saying that I need an increment of a day, #duration(1,0,0,0).
  • Since we have used the function List.Date, this Query1 is a list.

Next, click on Transform and convert into a table and rename this as a query as MyCal. 

When you are prompted for the To Table dialog box, you can simply hit ok as there is no delimiter we need to worry about.

Next, rename this column to Date by double-clicking it and type in the word Date. Alternatively you can also right click this column and rename it. While you are at it, click on the small icon ABC/123 to change the data type to Date. 

At this point, you have a column called Date in the table called MyCal and the date ranges from 01/01/2020 to 12/30/2020.

But wait, what happened to 31st??? Remember we chose the count as 365 earlier. hence it took 365 days from the StartDate of 01/01/2020.

Do not worry as we will modify the code in a short while to include the last day of the year.

Let us continue expanding this table.

  • Click on Add Column in the ribbon.
  • Click on the down arrow under the Date icon.
  • Click on Year, this will add a new column called Year.
  • Similarly, we can add Month, Quarter, Week, and Day columns in our table. 

At this point, we should have a decent date table. However, this is very static and we still have not taken care of the 31st of this year. To take care of that, just change the count to 366 and you should have all the days of this year.
But this is very brutish (because in the Year 2021 we will have 365 days instead of 366), in the next blog we will make this table more dynamic.

The complete M code is as follows, to get the same result as above just click on new blank query and click on advanced editor and past the following.

let
Source = List.Dates(#date(2020,1,1), 366, #duration(1,0,0,0)),
#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Renamed Columns” = Table.RenameColumns(#”Converted to Table”,{{“Column1”, “Date”}}),
#”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns”,{{“Date”, type date}}),
#”Inserted Year” = Table.AddColumn(#”Changed Type”, “Year”, each Date.Year([Date]), Int64.Type),
#”Inserted Month” = Table.AddColumn(#”Inserted Year”, “Month”, each Date.Month([Date]), Int64.Type),
#”Inserted Quarter” = Table.AddColumn(#”Inserted Month”, “Quarter”, each Date.QuarterOfYear([Date]), Int64.Type),
#”Inserted Week of Year” = Table.AddColumn(#”Inserted Quarter”, “Week of Year”, each Date.WeekOfYear([Date]), Int64.Type),
#”Inserted Day of Year” = Table.AddColumn(#”Inserted Week of Year”, “Day of Year”, each Date.DayOfYear([Date]), Int64.Type)
in
#”Inserted Day of Year”

This should give you a final result like below.

In the next post we will continue with the same date table to make it dynamic. If you have questions/queries/comments please do not hesitate to reach out to me.

Published by Ansh Purohit

I am a data enthusiast and enjoy working with data manipulation tools and languages. Power BI is my passion and I consider myself a life long learner. I enjoy sharing my knowledge with others because I firmly believe that Knowledge Increases by Sharing.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: