Script to Generate Date Dimension with Power Query M – With Financial Columns

There are many date dimensions scripts on the internet, and even there are some scripts for the Power Query (Thanks to Chris Webb for his very early version of the date dimension, and also to Matt Masson for his version of Date Dimension). I’ve built a date dimension from scratch (not a copy or extended version), because one of my reasons to build this dimension was Practice! So I did that to practice Power Query more in action. There are some other reasons that why I built this dimension.

Why I built the Date dimension;

• To use it: Date Dimension is one of the most common dimensions in Data Model. It is too rare to you see a data model without date dimension.
• I wanted to build a Power Query copy of my date dimension (previously I’ve done a date dimension with T-SQL)
• I used this as a practice to learn Power Query more in action.
• I need a demo for my upcoming speaking and presentations on Power Query.
• I like to share something useful in community.

The Date dimension that I’ve built contains:
-Financial calendar (configured in the config table)
-Weekend and Weekdays

*Note: This version of date dimension doesn’t contains public holidays, I’ll work on the newer version with public holidays and write another blog post about it.

Here is the script to build the Date Dimension:

let
// Read Config Table
ConfigTable=Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
YearsToAppend=Table.First(ConfigTable)[YearsToGenerate],
FinancialYearStartingMonth=Table.First(ConfigTable)[FinancialYearStartingMonth],
// Generate base table
Source = List.Dates(Date.From(Table.First(ConfigTable)[StartDate]),YearsToAppend*365,#duration(1, 0, 0, 0)),
Transformed=List.Transform(Source, each Date.ToRecord(_)),
Tabled=Table.FromList(Transformed,Record.FieldValues,{“Year”,”Month”,”Day”}),
//Add Full Date Column
FullDateAddedTable=Table.AddColumn(Tabled,”FullDateAlternateKey”,each Date.FromText(Text.From([Year])&”-“&Text.From([Month])&”-“&Text.From([Day]))),
DateKeyAdded=Table.AddColumn(FullDateAddedTable,”DateKey”,each ([Year]*10000)+([Month]*100)+[Day]),
FullDateNameAdded=Table.AddColumn(DateKeyAdded,”DateFullName”,each DateTime.ToText(DateTime.From([FullDateAlternateKey]),”dd MMMM yyyy”)),
// Fiscal Year
FiscalYearAdded=Table.AddColumn(FullDateNameAdded,
“Fiscal Year”,
each
if Date.Month([FullDateAlternateKey])>=FinancialYearStartingMonth then
Date.Year([FullDateAlternateKey])+1
else
Date.Year([FullDateAlternateKey])
),
// Fiscal Month
FiscalQuarterAdded=Table.AddColumn(FiscalYearAdded,
“Fiscal Quarter”,
each
if Date.Month([FullDateAlternateKey])>=FinancialYearStartingMonth then
Number.IntegerDivide((Date.Month([FullDateAlternateKey])-FinancialYearStartingMonth),3)+1
else
Number.IntegerDivide((12+Date.Month([FullDateAlternateKey])-FinancialYearStartingMonth),3)+1
),
// Calendar Quarter
CalendarQuarterAdded=Table.AddColumn(FiscalQuarterAdded, “Calendar Quarter”,
each Number.IntegerDivide(Date.Month([FullDateAlternateKey])-1,3)+1
),
// Is Week Day
WeekDayAdded=Table.AddColumn(CalendarQuarterAdded, “IsWeekDay”,
each
if
Date.DayOfWeek(DateTime.From([FullDateAlternateKey]))=Day.Sunday
or
Date.DayOfWeek(DateTime.From([FullDateAlternateKey]))=Day.Saturday
then 0 else 1 ),
// Day Of Week
DayOfWeek=Table.AddColumn(WeekDayAdded,”DayOfWeek”,each Date.DayOfWeek(DateTime.From([FullDateAlternateKey]))),
// Month Name
MonthName=Table.AddColumn(DayOfWeek,”Month Name”,each DateTime.ToText(DateTime.From([FullDateAlternateKey]),”MMMM”)),
// Day of Week Name
DayOfWeekName=Table.AddColumn(MonthName,”Day of Week Name”,each DateTime.ToText(DateTime.From([FullDateAlternateKey]),”dddd”))

in
DayOfWeekName

You can download the Sample workbook with config table and the script from here:

date-dimension-with-public-holidays-start

What I’ve learned through this example:

Loop Structure
As you probably noticed, Power Query (up to current version) doesn’t contain a loop structure.
This is a weakness for this version, but there are methods to do what you want with other structures. List and Table are structures that contains item and records. Fortunately List can be generated with some functions that called as GENERATORS. If you notice to the line of text with List.Dates, you would see that this line of code generates a list of dates from the start date with the duration specified up to specific number.
This line of code :

 Source = List.Dates(Date.From(Table.First(ConfigTable)[StartDate]),YearsToAppend*365,#duration(1, 0, 0, 0)),

Above line generates the list of dates, and below lines transform that date to a record containing day,month, and year, and finally it would be converted to a table with Table.FromList function:
    Transformed=List.Transform(Source, each Date.ToRecord(_)),
Tabled=Table.FromList(Transformed,Record.FieldValues,{“Year”,”Month”,”Day”}),

So as you seen above we generated a list from scratch, and then converted that to a table. you can apply expressions to a new column of the table with EACH single parameter function, and that means you can simulate loop like structure with List/Table structures. I would dedicate a blog post about this topic later with more illustration and detailed samples.

Date Functions

There are bunch of date functions used in this sample, such as :

Date.Month : returns the month of specific date

Date.DayOfWeek: returns enumeration values of the weekday, zero would be Sunday, and 6 would be Saturday

DateTime.ToText: generates a text FORMATTED from the date time with specified format.

There are many date functions which I will write about them in another blog post in future.

Reading information from Excel Workbook

In this script I used a config table, that table sits in an excel workbook, and I used lines below to read that table from the Excel workbook:

    // Read Config Table
ConfigTable=Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
YearsToAppend=Table.First(ConfigTable)[YearsToGenerate],
FinancialYearStartingMonth=Table.First(ConfigTable)[FinancialYearStartingMonth],

Having a config table such as the one above, helps to re-generate data with just a REFRESH button instead of changing hard-coded values in the script.

Save

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.