Another PowerQuery DateDimension query

This is nothing new and as already been blogued by Chris Webb and Matt Masson:

https://cwebbbi.wordpress.com/2013/11/19/generating-a-date-dimension-table-in-power-query/

http://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

But based on their work a made a new one with some new quickwins:

  • Suport for the day of the week start (in Portugal tipicaly starts on monday not sunday)
  • Only StartDate is mandatory, in this case a Date Dimension is generated til the end of the year
  • Some changes in the columns values

image

image


let CreateDateTable = (StartDate as date, optional EndDate as nullable date, optional Culture as nullable text, optional FirstDayOfWeek as nullable number) as table =>  
 let    
    Culture = if (Culture = null) then "pt-PT" else Culture,
    FirstDayOfWeek = if (FirstDayOfWeek = null) then 1 else FirstDayOfWeek, 
    EndDate  = if (EndDate = null) then Date.EndOfYear(StartDate) else EndDate,
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)) + 1,
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),    
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYear, "Quarter", each Date.QuarterOfYear([Date])),
    InsertMonth = Table.AddColumn(InsertQuarter, "Month", each Date.Month([Date])),
    InsertDay = Table.AddColumn(InsertMonth, "Day", each Date.Day([Date])),    
    InsertMonthName = Table.AddColumn(InsertDay, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthOfYear", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterOfYear", each "Q" & Number.ToText([Quarter]) & " " & Number.ToText([Year])),    
    InsertWeek = Table.AddColumn(InsertCalendarQtr, "Week", each Date.WeekOfYear([Date])),
    InsertCalendarWeek = Table.AddColumn(InsertWeek, "WeekOfYear", each "W" & Number.ToText([Week]) & " " & Number.ToText([Year])),
    InsertDayWeek = Table.AddColumn(InsertCalendarWeek , "WeekDay", each Date.DayOfWeek([Date], FirstDayOfWeek ) + 1),
    InsertDayName = Table.AddColumn(InsertDayWeek, "WeekDayName", each Date.ToText([Date], "dddd", Culture), type text),
    InsertDayInt = Table.AddColumn(InsertDayName, "DateId", each [Year] * 10000 + [Month] * 100 + [Day]),
    #"Changed Type" = Table.TransformColumnTypes(InsertDayInt ,{{"Year", Int64.Type}, {"Quarter", Int64.Type}, {"Month", Int64.Type}, {"Day", Int64.Type}, {"DateId", Int64.Type}, {"Week", Int64.Type}, {"WeekDay", Int64.Type}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type",{{"MonthName", Text.Proper}, {"MonthOfYear", Text.Proper}, {"WeekDayName", Text.Proper}})

  in
     #"Capitalized Each Word"
in
    CreateDateTable
Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s