I have a power query that pulls a list of files in a folder on my windows machine. I have to review these files, and somehow mark off when these files have been reviewed (so I can go back into the spreadsheet on the next day and know where I have left off). I’d prefer that this be done dynamically.

Files are sorted on the basis of date, so if I put a file in the folder from prior years (and would be earlier in the results of the query) it can go higher up in the query results, and all of the results get pushed down.

I have tried adding another column called “Revised_Date”; the problem is, that unless the new file goes at the very end, the revised date gets automatically filled in by the nearest values. So for instance, if I have

File Name |  File Creation Date| Reviewed Date
A         |     5/10/2024      |   5/10/2024
B         |     5/11/2024      |   5/11/2024
C         |     5/12/2024      |   5/12/2024

And I have a file D which has a review date of 5/13/2024 but a file creation date of 5/10/2024, power query will put the result before file A (or immediately after file A) and will give a reviewed date of 5/10/2024 (I’d be happy if it were blank).

Similar thing if I try to do it with column color – if I color all the columns, and the query inserts a new one in at some place, it gives the newly inserted row the same formatting.

I looked at table design -> external table data – > properties and after experimenting with each of the properties none of them do what I am looking for.

I saw some resources on the internet where someone made a second query, then appended from that second query a comment column and deleted the second query? Didn’t quite understand.