Summary of problem:

I have a PowerQuery Table in Excel that contains 13 columns. The 13th Column is a custom column "Task Start Week Number". I want the PowerQuery to apply a formula to each of the rows generated for this Query. The formula is as follows:

=IFS(AND('Program Dates'!$B$2<WEEKNUM(New_Items_to_Save[Start Date]),
WEEKNUM(New_Items_to_Save[Start Date])<54),
'Program Dates'!$G$2-('Program Dates'!$D$2-(-53+WEEKNUM(New_Items_to_Save[Start Date]))),
WEEKNUM(New_Items_to_Save[Start Date])<'Program Dates'!$B$2,
'Program Dates'!$G$2-('Program Dates'!$D$2-(-53+WEEKNUM(New_Items_to_Save[Start Date])))+53)

What I've done here is reference a cell which contains the formula, that way I can just run the GetValue() function for a named range. I can't get this to work and I don't know what I'm doing wrong.

Thank you in advance for your help!

Context:

This is the query table I need to add the calculation to.

The last column is the custom column, and those values should be calculated using the following cells:

This is the source of the other info needed to calculate the week number of the program, with reference arrows shown.

Note: The dates referenced in the function have already been converted using the WEEKNUM() operation. I am comparing Week# to Week#, not Date to Week#

Function Logic:

AND: if the date falls within the range of the current year ie. week# is less than 54, but after the start of the program, then perform this calc.

IFS: otherwise, if week# is before the end of the program ie. 2023, then perform this calculation.

Edit:

Here is the PowerQuery function I want to call for each of the new cells in this custom column:

Parameter2 = Date.WeekOfYear(StartWeek)
let
    GetWeek = ()
    if GetValue("Start_Week") < Parameter2 < 54
    then (GetValue("Program_Duration") - GetValue("End_Week") + 53 - Parameter2)) 
    else
    (GetValue("Program_Duration") - GetValue("End_Week") + 53 - Parameter2 +53))
in 
    GetWeek

I don't know if I need the let statement or if I should just put it in a function

f(x) => [equation]

and then call "...each f([column name])" in power query?