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?
Assuming "A" contains the date (and is type date) of the data entry for the columns on that row, then insert this code below where you want to "null" the old records.
nullOldData = Table.FromRecords(
Table.TransformRows(#"Previous Step",
(r)=>
Record.TransformFields(
r,
{
{"B", each if Duration.Days(Date.From(DateTime.LocalNow())- r[A]) > 90 then null else _ },
{"C", each if Duration.Days(Date.From(DateTime.LocalNow())- r[A]) > 90 then null else _ },
{"D", each if Duration.Days(Date.From(DateTime.LocalNow())- r[A]) > 90 then null else _ }
})
)),
Replace #"Previous Step" with the name of the preceding step in the Editor.
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