Demystifying Date Dimension Part 3

Hello All, let us take a deep dive into Date Dimension Part 3

However, for the next few 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 you recall the three main components that I spoke about, StartDate, EndDate, and the Duration from previous posts. Once again, I will reiterate that this is all that we need for any date table. 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 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, and it simply means what the duration or increment of each instance is. 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 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, and 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, 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, click on the new blank query and click on the advanced editor, and past the following.

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)
#”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.

Categories: Date Dimension

Tags: , , , , ,

Leave a Reply

Thank you for subscribing to my blog

There was an error while trying to send your request. Please try again.

Datum to Data will use the information you provide on this form to be in touch with you and to provide updates and marketing.
%d bloggers like this: