Forum Discussion
Replacing a cell value to null based on time
Hi,
I have put in the code in the advanced editor as another step in the query. I tried replacing the values BCD and A by their corresponding column title.. However, I get the "invalid identifier" because two of those columns have "when" in their title (even if I put the column title between the "").
For instance, Column B is "Email", C is "Name", D is "Please add your resignation letter (format : LastName,FirstName,AAAA-MM-DD)" and A (date column) is "When is your last day at COMPANY?".
Is there a way to make column A's title not be an identifier problem, even though it has When in it?
Here is how I adapted it:
NullData = Table.FromRecords(
Table.TransformRows(#"Filtered Rows",
(r)=>
Record.TransformFields(
r,
{
{"Email", each if Duration.Days(Date.From(DateTime.LocalNow())- r[#"When is your last day at COMPANY?"]) > 90 then null else _ },
{"Name", each if Duration.Days(Date.From(DateTime.LocalNow())- r[#"When is your last day at COMPANY?"]) > 90 then null else _ },
{"Please add your resignation letter (format : LastName,FirstName,AAAA-MM-DD)", each if Duration.Days(Date.From(DateTime.LocalNow())- r[#"When is your last day at COMPANY?"]) > 90 then null else _ }
})
)),
Are you spotting anything that could explain it?
I see no problems in that line of code. Obviously you cannot post confidential data. But perhaps you could create some data that, together with the rest of your code, can be used to reproduce the problem you are seeing.
I have tried putting it as a step before filtering rows, but now I get this error:
To give you an idea of what it looks like, I have attached a quick screenshot of the editor. Am I putting the code in the wrong place? Apologies, I feel like the problem might come from me.