Extending the PowerQuery date table generator to include ISO Weeks

Chris Webb and Matt Mason have both blogged about formulas for generating  a date table using PowerQuery, but both of these posts focus on the standard year-month-day calendar. I’ve been doing a little work with some week based calculations and thought I would see how hard it would be to extend this sort of approach to generate some columns for a week hierarchy.

The ISO Week standard is part of ISO 8601 and defines a week as starting on Monday and ending on Sunday. That in itself is not very hard. The tricky bit comes into play when you go to assign each week to a year. Because weeks don’t fit evenly into years you need to either move some days from the end of December forward or move a few days of January back to the prior year.

The way to do this is as follows:

  • Find the week that contains January 4 as that is always the first week of the year.
  • If Jan 4 is before Thursday then any January days prior to Monday are allocated to the previous year.
  • If Jan 4 is after Thursday then any December days at the start of the week are treated as being part of the current year.


I’ve also taken this a step further and created a small inline function that figures out the current 4-4-5 period and quarter that a given week falls into. I’m using a function which returns a record to return both the period and quarter from the one function which I think is pretty cool.

The following is an extension of Matt Mason’s method, he has some great screen shots of how to use the function so if you have not seen that post it’s definitely worth checking out.

Basically you start a new blank query, switch to the advanced mode and then paste in the following and invoke it:

let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
  let
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
    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, "QuarterOfYear", each Date.QuarterOfYear([Date])),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
    InsertFinYearNumber = Table.AddColumn(InsertMonth, "FinYearNumber",each if [MonthOfYear] >= 7  then [Year] else [Year] -1 ),
    InsertFinYearDisplay = Table.AddColumn(InsertFinYearNumber, "FinYear" ,each "FY" & Text.End(Number.ToText([FinYearNumber],"D",""),2) & "/" & Text.End(Number.ToText([FinYearNumber]+1,"D",""),2)),
    InsertFinMonth = Table.AddColumn(InsertFinYearDisplay, "FinMonth", each if [MonthOfYear] >= 7  then [MonthOfYear] - 6 else [MonthOfYear] + 6 ),
    InsertFinQuarterNumber = Table.AddColumn(InsertFinMonth, "FinQuarterNumber", each if [QuarterOfYear] > 2 then [QuarterOfYear] -2 else [QuarterOfYear] + 2),
    InsertFinQuarterDisplay = Table.AddColumn(InsertFinQuarterNumber ,"FinQuarter", each "FQ" & Number.ToText([FinQuarterNumber],"D","") ),

    InsertDay = Table.AddColumn(InsertFinQuarterDisplay , "DayOfMonth", each Date.Day([Date])),
    InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
    InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date],1)+1),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEndingFriday", each Date.EndOfWeek([Date],6), type date),   
    InsertCurrentThursday = Table.AddColumn(InsertWeekEnding, "CurrentThursday", each Date.AddDays([Date], -Date.DayOfWeek([Date],1) + 3), type date),
    InsertISOWeekJan4 = Table.AddColumn(InsertCurrentThursday, "ISOWeekJan4", each Date.FromText(Number.ToText(Date.Year([CurrentThursday])) & "-01-04") ,type date),
    InsertISOWeekYear = Table.AddColumn(InsertISOWeekJan4, "ISOWeekYear", each Date.Year([CurrentThursday])) ,  
    InsertISOWeekFirstMon = Table.AddColumn(InsertISOWeekYear, "ISOWeekFirstMon", each
        if [CurrentThursday] < [ISOWeekJan4]
        then Date.AddDays([CurrentThursday],-3)
        else Date.AddDays([ISOWeekJan4], - Date.DayOfWeek([ISOWeekJan4],1) )
      ,type date),
    InsertISOWeekNum = Table.AddColumn(InsertISOWeekFirstMon, "ISOWeekNum", each Number.RoundUp(((Duration.Days(Duration.From([Date] - [ISOWeekFirstMon]))+1) /7 )), type number),
    InsertISOWeekID = Table.AddColumn(InsertISOWeekNum, "ISOWeekID", each [ISOWeekYear] * 100 + [ISOWeekNum], type number),
    InsertISOWeekName = Table.AddColumn(InsertISOWeekID, "ISOWeekName", each Text.From([ISOWeekYear]) & "W" & Text.End( "0" & Text.From(([ISOWeekNum]*10)  + [DayInWeek]),3)),
    InsertISOWeekNameLong = Table.AddColumn(InsertISOWeekName, "ISOWeekNameLong", each Text.From([ISOWeekYear]) & "-W" & Text.End( "0" & Text.From([ISOWeekNum]),2) & "-" & Text.From([DayInWeek])),

    fnPeriod445a = (weekNum) => let
      Periods =
        {
            {(x)=>x<5,  [P=1,Q=1]},
            {(x)=>x<9,  [P=2,Q=1]},
            {(x)=>x<14, [P=3,Q=1]},
            {(x)=>x<18, [P=4,Q=2]},
            {(x)=>x<22, [P=5,Q=2]},
            {(x)=>x<27, [P=6,Q=2]},
            {(x)=>x<31, [P=7,Q=3]},
            {(x)=>x<35, [P=8,Q=3]},
            {(x)=>x<40, [P=9,Q=3]},
            {(x)=>x<44, [P=10,Q=4]},
            {(x)=>x<48, [P=11,Q=4]},
            {(x)=>true, [P=12,Q=4]}
        },
      Result = List.First(List.Select(Periods, each _{0}(weekNum))){1}
    in
      Result,

    InsertPeriod445 = Table.AddColumn(InsertISOWeekNameLong, "Period445Record", each fnPeriod445a([ISOWeekNum])),
    ExpandPeriod445 = Table.ExpandRecordColumn(InsertPeriod445, "Period445Record", {"P","Q" }, {"Period445", "Quarter445"}),
    RemovedColumns = Table.RemoveColumns(ExpandPeriod445,{"CurrentThursday", "ISOWeekFirstMon"})
  in
    RemovedColumns
in
    CreateDateTable

Update 18 Jul 2017: Added Financial Year and Quarter columns

Print | posted on Sunday, March 23, 2014 10:11 PM

Comments on this post

# re: Extending the PowerQuery date table generator to include ISO Weeks

Requesting Gravatar...
Hi,
when trying to instantiate the function like this:
CreateDateTable(#date(2014, 1, 1), #date(2020, 12, 31), "en-US")
I get wired dates. Is there an overflow:
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
Left by Artur on Nov 27, 2014 11:42 PM

# re: Extending the PowerQuery date table generator to include ISO Weeks

Requesting Gravatar...
What is weird about the dates can you post an example? When I invoke the function with those parameters it appears to work fine.
Left by Darren Gosbell on Nov 28, 2014 6:19 AM

# re: Extending the PowerQuery date table generator to include ISO Weeks

Requesting Gravatar...
You my friend are a genius - thank you for this.
Left by Llew on Nov 18, 2015 10:11 PM

# re: Extending the PowerQuery date table generator to include ISO Weeks

Requesting Gravatar...
Works Great!
Left by Serge on Feb 10, 2016 5:17 AM

# re: Extending the PowerQuery date table generator to include ISO Weeks

Requesting Gravatar...
An imnprovement would be to set up properly the data types where needed.

"Int64.Type" for the numbers (and not type number, that is decimal)
"type text" to avoid default type "Any"

This way you boost performance in Power Query and in PowerPivot.

Example:
InsertISOWeekYear = Table.AddColumn(InsertISOWeekJan4, "ISOWeekYear", each Date.Year([CurrentThursday]), Int64.Type) ,
Left by Fred Lorrain on Apr 05, 2016 4:00 PM

# re: Extending the PowerQuery date table generator to include ISO Weeks

Requesting Gravatar...
Hi, your code works perfect!! Thanks.

I would like to add an extra column with ISOquarter.

Can anybody help me, I'm a complete noob.

Thanks.
Erik
Left by Erik on Apr 06, 2016 4:33 AM

# re: Extending the PowerQuery date table generator to include ISO Weeks

Requesting Gravatar...
FWIW, 4-4-5 Q number for 4th month should be {(x)=>x<18, [P=4,Q=2]}

Also, the test for the last month of the year {(x)=>true, [P=12,Q=4]} can give wrong result e.g. for 2015: the year has 53 weeks, in 4-4-5 the last week of the year is week number 52, else month 12 gets 6 weeks, making it a 4-4-6 - or rather a 4-4-5.5 scheme :-)
Interestingly, this means that the whole year 2016 is then off 1 week in numbering the 4-4-5 months and quarters, as the year in 4-4-5 starts with week 53 of 2015...
Left by -EM on Jun 03, 2016 12:42 AM

# re: Extending the PowerQuery date table generator to include ISO Weeks

Requesting Gravatar...
Hi Erik

I use this in my code for financial quarter, but haven't been able to work out how to add it into this excellent piece of work.

What I did was add a column and entered the following:
Table.AddColumn(#"Invoked FunctionCreateDateTable", "FinancialYearQuarter", each if [ISOWeekNum] <= 13 then "FY"&Text.End(Number.ToText([ISOWeekYear]-1),2)&"Q4" else if [ISOWeekNum] <= 26 then "FY"&Text.End(Number.ToText([ISOWeekYear]),2)&"Q1" else if [ISOWeekNum] <= 39 then "FY"&Text.End(Number.ToText([ISOWeekYear]),2)&"Q2" else "FY"&Text.End(Number.ToText([ISOWeekYear]),2)&"Q3")
Left by Martin Lucas on Jun 30, 2016 6:36 PM

# re: Extending the PowerQuery date table generator to include ISO Weeks

Requesting Gravatar...
How can I change the quarter to show FY QTR?
I.E Quarter 1 starts July 1, Quarter 2 starts Oct 1st and so on...
Left by Mark on Jul 18, 2017 8:59 AM

# re: Extending the PowerQuery date table generator to include ISO Weeks

Requesting Gravatar...
@Mark - I've updated the blog post to include Financial Years and Quarters based on a Jul 1 start of year as an example
Left by Darren Gosbell on Jul 18, 2017 1:10 PM

# re: Extending the PowerQuery date table generator to include ISO Weeks

Requesting Gravatar...
First off, this is fabulous!

I've just started learning DAX so this script is teaching me tons.

Got a question We have a July 1 thru Jun 30 financial calendar. I'm using this script to build a calendar for weekly slicers. How do I change the WeekEndingFriday to WeekEndingSunday?

We are a 24x7 shop. So our weeks are Monday thru Sunday. So I need the dates in the WeekEndingSunday column to fall on a Sunday not a Friday.

Thanks for sharing

Bev
Left by Bev on Mar 10, 2018 10:01 AM

# re: Extending the PowerQuery date table generator to include ISO Weeks

Requesting Gravatar...
@Bev - actually the ISO Week goes from Mon to Sun, I think the W/E Fri is a left over artifact from an internal model. To change to a W/E Sun is as simple as changing the following line:

InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEndingFriday", each Date.EndOfWeek([Date],6), type date),

to this (changing the column name and the EndOfWeek day number):

InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEndingSunday", each Date.EndOfWeek([Date],1), type date),
Left by Darren Gosbell on Mar 13, 2018 10:12 AM

# re: Extending the PowerQuery date table generator to include ISO Weeks

Requesting Gravatar...
Sir, you are a gentleman and a scholar.
Cannot thank you enough for this.
Left by MW on May 09, 2018 1:48 AM
comments powered by Disqus