How to efficiently create a weeks calculator

Hi dolphin18 

The following m code should help you,

Just rename YourPreviousStepName by the name of your previous step

let
    DesiredWeekStartDate = #date(2025, 1, 1),

    AddWeekNumberColumn = Table.AddColumn(
        YourPreviousStepName,
        "Week",
        each
            let
                DaysDifference = Duration.Days([Start Date] - DesiredWeekStartDate ),
                WeekNumber =
                    if DaysDifference >= 0 then
                        "Week " & Text.From(Number.RoundDown(DaysDifference / 7))
                    else
                        null
            in
                WeekNumber,
        type text
    )
in
    AddWeekNumberColumn

let
    start_week = Date.StartOfWeek(#date(2025, 6, 24)),
    Source = #table(
        {"ID", "Start Date"}, 
        {
            {"A", #date(2025, 6, 23)}, 
            {"B", #date(2025, 7, 1)}, 
            {"C", #date(2025, 8, 11)}, 
            {"D", #date(2025, 4, 2)}
        }
    ), 
    week_no = Table.AddColumn(
        Source, 
        "Week", 
        (x) => ((days_between) => if days_between < 0 
            then null 
            else days_between / 7
        )(Duration.Days(Date.StartOfWeek(x[Start Date]) - start_week))
    )
in
    week_no