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?
I think it is the "?" that is causing the problem.
Try [#"When is your last day at COMPANY?"] for the Record Field Name
The code syntax seems correct (as per PowerBI), but when I let the query run that step, the table disappears and all that is visible is FALSE.
Most likely there is an issue with how you adapted my code to your actual data. There is nothing in my code that should return FALSE.
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.