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

Posted by on Feb 14, 2014 in Power BI, Power Query | 6 Comments
Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

 

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

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

6 Comments

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">