Expression.Error: The field 'Date' of the record wasn't found.

[Rohit001]

I am trying to create a forecast value but when applying this query it is showing some error 
let
Source = Source,
AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type), // Add an index column
AddMovingAverage = Table.AddColumn(AddIndex, "MovingAverage", each
let
currentIndex = [Index],
previousThreeValues = List.FirstN(List.Reverse(Table.Column(AddIndex, "SalesValue")), currentIndex),
avg = List.Average(previousThreeValues)
in
if currentIndex > 3 then avg else null
),
RemoveIndex = Table.RemoveColumns(AddMovingAverage, {"Index"}), // Remove the index column
AddForecastedValue = Table.AddColumn(RemoveIndex, "ForecastedValue", each
let
currentDate = [Date],
nextDateRow = Table.SelectRows(RemoveIndex, each [Date] = currentDate + #duration(1, 0, 0, 0)),
nextDateValue = if Table.RowCount(nextDateRow) > 0 then nextDateRow{0}[SalesValue] else null,
movingAverage = [MovingAverage]
in
if nextDateValue <> null and movingAverage <> null then nextDateValue + movingAverage else null
),
RemoveMovingAverage = Table.RemoveColumns(AddForecastedValue, {"MovingAverage"}) // Remove the intermediate MovingAverage column
in
RemoveMovingAverage

[spinfuzer · Accepted solution]

You seem to be working with nested Tables.  You have at least two each within your Table.AddColumn.  I recommend using (x) => x[Date] instead of each [Date] for your nested table.  Now x[Date] will refer to your nested date and [Date] will refer to your outer date (if it exists).

[Rohit001]

Ohk

[collinsg]

Hello Rohit001,

I'm not sure if this will be enough to solve your problem but it should be a step in the right direction. I notice your "Date" column is of type text. The query would therefore trip up on the step,

currentDate + #duration(1, 0, 0, 0))

If you change the column to type date before your calculation you may get closer to a solution.

Hope this helps.