The field 'ExpiryDateTest' of the record wasn't found. When creating a Custom Column

[ElsieCassin]

Hi,

I think I may have the Syntax for my Custom Column wrong, because my column ExpiryDateTest exists before the step to create the Custom column, but it dissappears after the step (see screenshots below)

My Add Custom1 command is:
Table.AddColumn(#"Renamed Columns", "Expired", each if [#"ExpiryDateTest"] <= DateTime.Date(DateTime.LocalNow()) then "Expired" else "Not-Expired").

Can anyone help me with what I have wrong in my Add Custom syntax?

Many thanks..

[PC2790]

Try:

Table.AddColumn(#"Renamed Columns", "Expired", each if [ExpiryDateTest] <= DateTime.Date(DateTime.LocalNow()) then "Expired" else "Not-Expired").

[ElsieCassin]

Hi PC2790,

Thanks for your rply. 

I believe I've already tried this, but I copied and pasted your syntax and get the same message.

Just to clarify I see the below after creating the Custom Column

Then when I click on the field selector, its shows that the ExpriyDateTetst field is still there, but its not in the selection list.  
When I de-select everything but the ''Expired' field, the ExpiryDateTest field disappers from the and all the rows in the collumn show Error.
Before creating the custom field, I used the ExpiryDateTest columnin a report and it brought back data.

[PC2790 · Accepted solution]

I see that the previos step before Added Custom1 is "Changed Type1", however you are using "Renamed Columns"

Can you once try:

Table.AddColumn(#"Changed Type1", "Expired", each if [#"ExpiryDateTest"] <= DateTime.Date(DateTime.LocalNow()) then "Expired" else "Not-Expired")

If this doesn't work, please provide the sample file removing all the sensitive data to have a better understanding of the issue.

[ElsieCassin]

Hi PC2790,

Thanks for your help.  Yes apologies, I'd missed that I was still referrring to an old step!  I'll mark as solved!  Cheers...

[amitchandak]

@ElsieCassin , I doubt the column name , it should be simply ExpiryDateTest

While creating a column, select from the right had side selection

if [ExpiryDateTest] <= DateTime.Date(DateTime.LocalNow()) then "Expired" else "Not-Expired"

[ElsieCassin]

Hi amitchandak,
Yes I tried this also, but see the same result