## 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

- Share This Post:
- Short Url: http://wblo.gs/eW9

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

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()),

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

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

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

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

"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) ,## # re: Extending the PowerQuery date table generator to include ISO Weeks

I would like to add an extra column with ISOquarter.

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

Thanks.

Erik

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

`{(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...

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

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")

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

I.E Quarter 1 starts July 1, Quarter 2 starts Oct 1st and so on...

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

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

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

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

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),

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

Cannot thank you enough for this.