ISO Day Of Year

[Troops]

Hi

I use a query that I found a long time ago to generate a calendar/date table which I use to report on metrics across ISO weeks/years i.e where the year begins on the first Monday in January.

However I need to adapt it to get an ISO day of year (i.e a number between 0 and 365 or up to 372 in a 53 week year).

What do I need to add to the queryt to do this? For reference the current query is 

= (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])), 
    InsertDay = Table.AddColumn(InsertMonth, "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), 
    InsertWeekCommencing = Table.AddColumn(InsertDayName, "WeekCommencing", each Date.StartOfWeek([Date],1), type date),    
    InsertCurrentThursday = Table.AddColumn(InsertWeekCommencing, "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 

[Nolock · Accepted solution]

Hi @Troops,

you have already everything you need in your code, you just need to add an additional column which does following: 

= Table.AddColumn(RemovedColumns, "ISODayOfYear", each Duration.Days([Date]-[ISOWeekFirstMon]) + 1)

When I modify your code, it'll be:

= (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])), 
    InsertDay = Table.AddColumn(InsertMonth, "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), 
    InsertWeekCommencing = Table.AddColumn(InsertDayName, "WeekCommencing", each Date.StartOfWeek([Date],1), type date),    
    InsertCurrentThursday = Table.AddColumn(InsertWeekCommencing, "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])),
ISODayOfYear = Table.AddColumn(InsertISOWeekNameLong , "ISODayOfYear", each Duration.Days([Date]-[ISOWeekFirstMon]) + 1),

    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(ISODayOfYear, "Period445Record", each fnPeriod445a([ISOWeekNum])), 
    ExpandPeriod445 = Table.ExpandRecordColumn(InsertPeriod445, "Period445Record", {"P","Q" }, {"Period445", "Quarter445"}), 
    RemovedColumns = Table.RemoveColumns(ExpandPeriod445,{"CurrentThursday", "ISOWeekFirstMon"}) 
  in RemovedColumns

The result: