Forum Discussion
Replacing a cell value to null based on time
Hi all,
I am working on a dashboard and, for data integrity's sake, have to remove some cell values from a few columns, based on how old the data is.
For example, I have a table of 100 rows, ranging from column A to M. I would like to remove/hide/replace by null all the data(cells) in column B, C and D that are older than 90 days, so that they do not show (but so I can still use the rest of the data in those rows).
I have looked into pretty much anything that goes with replacing values, hiding columns or rows, to no avail.
I cannot post the file as it is confidential information, but I will try my best to answer or give additional information.
Thank you very much.
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?
9 Replies
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
