Hi @r-b-osei,
See how you get on with this query.
let
fxFY = ( years as list ) as table => [
t = List.Transform( years, each [
n = _ - List.First(years),
FYs = List.Repeat( {_}, 52),
FYID = List.Repeat( {n+1}, 52),
Weeks = {1..52},
FWID = List.Transform( Weeks, (x)=> (52 * n) +x ),
FYPeriod = List.Transform({0..51}, (x)=> Number.IntegerDivide(x, 4) +1),
FPID = List.Transform(FYPeriod, (x)=> (13 * n) +x ),
StartDate = List.Transform( {0..51}, (x)=> Date.StartOfWeek( Date.AddDays( #date(_, 4, 1), x *7), Day.Saturday )),
EndDate = List.Transform( {0..51}, (x)=> Date.EndOfWeek( Date.AddDays( #date(_, 4, 1), x *7), Day.Saturday )),
Dates = List.Transform( StartDate, (x)=> List.Dates(x, 7, Duration.From(1))),
t = Table.FromColumns(
{
FYs, FYID, Dates, Weeks, FWID, StartDate, EndDate, FYPeriod, FPID
}, type table [
FY=Int64.Type, FY ID=Int64.Type, Date={date}, Week=Int64.Type, FW ID=Int64.Type, StartDate=date, EndDate=date, FY Period=Int64.Type, FP ID=Int64.Type
]
)
][t] ),
r = Table.ExpandListColumn( Table.Combine(t), "Date")
][r],
// Invoke custom function on a list of year numbers
Source = fxFY( {2020..2025} ),
// Use UI to extend this custom date table with CY attributes
InsertYear = Table.AddColumn(Source, "CY", each Date.Year([StartDate]), Int64.Type)
in
InsertYear
Remarks:
- The fxFY is a custom function that operates on a list of year numbers.
- The Source step, illustrates how to invoke this custom function
- You can use Add Column, From Date options to extend this date table further, as demonstrated by the InsertYear step of this query
- In my view ID columns are just as powerful as Offsets. They easily convert to Offsets, if you require them. Follow the instructions illustrated here.
I hope this is helpful
