I’m encountering an error while trying to create a custom column in Power Query in Excel. The goal is to implement two tests:

1. If the “PPL” column value for a row is zero, return the date from the “Date” column.
2. Subtract the number of days specified in the “n” column from the date in the “Date” column. If the resulting date has a “PPL” value of zero, find the maximum date where “PPL” is greater than zero and the date is less than the calculated date. If no such date exists, return the calculated date.

PPL is Production Plan
DTP is Delivered to Plan.

let
// Extract values for the current row
currentDate = [Date],
units = [PPL],
n = [n],

// Logic for test 1: If Units in Date equals zero then return Date
test1 = if units = 0 then currentDate else

// Logic for test 2: Detract number of days equal to the number in n column
let
    subtractedDate = Date.AddDays(currentDate, -n),
    maxDate = List.Max(
                Table.SelectRows(
                    Tabela2, 
                    each [Date] < subtractedDate and [PPL] > 0
                )[Date]
              )
in
    // If unity number in the calculated date equals zero, search for maximum date
    // where unity is bigger than zero, but date is less than calculated date
    if Date.IsInPreviousNMonths(subtractedDate, 1) and 
       (List.IsEmpty(Table.SelectRows(Tabela2, each [Date] = subtractedDate and [PPL] > 0))) then 
           maxDate 
    else 
        if units = 0 then subtractedDate else currentDate

in
test1

Date PPL item n DTP
08/01/2024 0 a 2 08/01/2024
08/01/2024 0 b 5 08/01/2024
08/01/2024 0 c 0 08/01/2024
09/01/2024 0 a 2 09/01/2024
09/01/2024 0 b 5 09/01/2024
09/01/2024 0 c 0 09/01/2024
10/01/2024 0 a 2 10/01/2024
10/01/2024 0 b 5 10/01/2024
10/01/2024 0 c 0 10/01/2024
11/01/2024 0 a 2 11/01/2024
11/01/2024 0 b 5 11/01/2024
11/01/2024 0 c 0 11/01/2024
12/01/2024 0 a 2 12/01/2024
12/01/2024 0 b 5 12/01/2024
12/01/2024 0 c 0 12/01/2024
13/01/2024 92 a 2 13/01/2024
13/01/2024 92 b 5 13/01/2024
13/01/2024 92 c 0 13/01/2024
14/01/2024 720 a 2 14/01/2024
14/01/2024 720 b 5 14/01/2024
14/01/2024 720 c 0 14/01/2024
15/01/2024 350 a 2 15/01/2024
15/01/2024 350 b 5 15/01/2024
15/01/2024 350 c 0 15/01/2024
16/01/2024 726 a 2 16/01/2024

Adjustments to code, logic improvements