Help on creating dynamic Period & week column using Power Query

Power Query
1

Hello Team,

I have dates column starting from 26-Dec-2021 where I need to create Period Column for every 28 days and Weeks column for every 7 days with the particular period like the example shared below in the attached sheet, this should be dynamic where starting from this year 26-Dec-2022 it has to start again from Period 1.

any help using Power query ?

Period Table Automation.xlsx (10.3 KB)

Thanks
Dharma S

2

Hi @Dharma,

This does exactly what you’ve described. Although you might want to look into how the last day(s) of the year are handled.

Copy the code below into a new blank query and invoke it. This requires two parameters a start of year and the number of years.

(StartDateYear as date, NumOfYears as number ) as table =>
    let
        StartOfYear = List.Generate(
            ()=> StartDateYear,
            (x)=> x < Date.AddYears(StartDateYear, NumOfYears),
            (x)=> Date.AddYears( x, 1)
        ),
        DatesInYear = List.Transform(
            {0..NumOfYears-1},
            each List.Dates( StartOfYear{_}, Number.From(Date.AddYears( StartOfYear{_}, 1) - StartOfYear{_}), Duration.From(1) )
        ),
        DayInYear = List.Transform(
            {0..NumOfYears-1},
            each List.Transform( DatesInYear{_}, (x)=> Number.From( x - DatesInYear{_}{0} ) )
        ),
        PeriodInYear = List.Transform(
            {0..NumOfYears-1},
            each List.Transform( DayInYear{_}, (x)=> "Period " & Text.From(  Number.IntegerDivide( x, 28)+1 ))
        ),
        DayInPeriod = List.Transform(
            {0..NumOfYears-1},
            each List.Transform( DatesInYear{_}, (x)=> "Week " & Text.From( Number.IntegerDivide( Number.From( x - Date.AddDays( DatesInYear{_}{0}, 28 * Number.IntegerDivide( DayInYear{_}{ List.PositionOf( DatesInYear{_}, x) }, 28))), 7)+1 ))
        ),
        NestTable = List.Transform(
            {0..NumOfYears-1},
            each Table.FromColumns( 
                {
                    DatesInYear{_}, PeriodInYear{_}, DayInPeriod{_}
                },
                { "Date", "Period", "Week" }
            )
        ),
        ToTable = Table.Combine( NestTable ),
        ChType = Table.TransformColumnTypes(ToTable,{{"Date", type date}, {"Period", type text}, {"Week", type text}})
    in 
        ChType

.
With this result.

image
image524×818 28.7 KB

Here’s my PBIX
eDNA - custom dates, period and week.pbix (12.6 KB)

I hope this is helpful

4 Likes
3

@Melissa Thank you