Create week number start with 1st July

[Imi_Jay]

Hi ,
I am using Excel 365 , Power Query.

I want to create a column that shows week starting from 1/7/2022 to 31/6/2023 where week 1 start from 1/7/2022.

How can I do it?

Thanks!

[#2]

Hi @Imi_Jay 

(31/6/2023 doesn't exist, adjusted to 30/6/2023 below). Not sure where/how you want to add that column so the following might not be exactly what you want... (Edited)

let
    StartDate = #date(2022,7,1),
    EndDate = #date(2023,6,30),
    Days = Duration.Days(EndDate - StartDate) +1,
    Weeks = Number.RoundUp(Days / 7),
    Table = Table.FromColumns(
        {
            List.Dates(StartDate, Days, #duration(1,0,0,0)),
            List.FirstN(
                List.TransformMany(List.Numbers(1, Weeks), each
                    List.Numbers(1, 7), (w,d)=> "Week " & Text.From(w)
                ),
                Days
            )
        },
        type table [Date=date, Fiscal Week=text]
    )
in
    Table

This creates a Table of 2 columns:

[#3 · Accepted solution]

@Imi_Jay 

To play with variants

Power Query

let
    Start = Number.From(#date(2022,7,1)),
    End = Number.From(#date(2023,6,30)),
    Date = Table.FromList({Start..End}, Splitter.SplitByNothing(), {"Date"} ),
    Week = Table.AddColumn(
        Date,
        "Week #",
        each "Week " & Text.From(Number.IntegerDivide([Date] - Start, 7) +1),
        type text),
    DateFormat = Table.TransformColumnTypes(Week,{{"Date", type date}})
in
    DateFormat

Dynamic array

=LET(
   start,   DATE(2022,7,1),
   end,    DATE(2023,6,30),
   date,   SEQUENCE( end-start+1,,start),
   week, "Week " & INT((date-start)/7 ) + 1,
 IF({1,0}, date, week ) )

[#4]

Thanks!