Power Query date

Question

Hi Experts,
I am working on Power query(Excel) to generate list of dates for the Fiscal Years
Fiscal Year starts with May 1st week of every Year and ends on April last week. Each start date should be on friday
For Example FY24 WK01 is 5/5//2023

I would like to create a Mquery code in Excel that needs to look like below

FiscalWeek	Date
FY24 WK01	5/5/2023
FY24 WK02	5/12/2023
.	.
FY25 WK01	5/3/2024

I have created the Power query which gives wrong dates bas shown below, could you please correct the code to meet my above requirements

let

StartYear = 2023, 

EndYear = 2035, 

StartDate = Date.From(Date.From(Date.FromText("5/1/" & Text.From(StartYear)))), 

DatesList = List.Generate( 

    () => [Date = StartDate, FiscalWeek = 1, Year = StartYear], 

    each [Date] <= Date.From(Date.From(Date.FromText("5/1/" & Text.From(EndYear)))), 

    each [ 

        Date = Date.AddDays([Date], 7), 

        FiscalWeek = if Date.Month([Date]) = 5 and Date.Day([Date]) <= 7 then 1 else [FiscalWeek] + 1, 

        Year = if Date.Month([Date]) = 5 and Date.Day([Date]) <= 7 then [Year] + 1 else [Year] 

    ], 

    each [FiscalWeek = "FY" & Text.From([Year] - 2000) & " WK" & Text.PadStart(Text.From([FiscalWeek]), 2, "0"), Date = [Date]] 

), 

FridaysList = List.Transform(DatesList, each [FiscalWeek = [FiscalWeek], Date = Date.AddDays([Date], 5)]), 

OutputTable = Table.FromList(FridaysList, Splitter.SplitByNothing(), {"Fiscal Week"}), 

#"Expanded Fiscal Week" = Table.ExpandRecordColumn(OutputTable, "Fiscal Week", {"FiscalWeek", "Date"}, {"FiscalWeek", "Date"}) 

in

#"Expanded Fiscal Week"  

FiscalWeek	Date
FY23 WK01	5/6/2023
FY24 WK01	5/13/2023
FY24 WK02	5/20/2023
FY24 WK03	5/27/2023
FY24 WK04	6/3/2023
.	.
.	.
.	.
FY25 WK01	5/18/2024

Answer 1

Excel 365 Pro Plus with Power Pivot and Power Query.

Example shown with 4-4-5 Reporting Fiscal Calendar for 2021 starting in April on a Sunday.

Oodles of Reporting, Year, Month, Day variations in use.

Some US federally approved.

Program as needed.

https://www.mediafire.com/file_premium/xmvorui85f2ws0j/05_26_23.pdf/file https://www.mediafire.com/file_premium/omhe03d2nkepva3/05_26_23.xlsx/file