In our process, we download an error file on a daily basis.
What we would like to do, is using the concatenation of columns E and K as a unique record, compare the old file to the new file.
We need to capture the date added for any new records added, otherwise the original date added and comments are maintained.
Records without a match in the new file, are considered fixed and may be deleted.
I was thinking it could be some type of append process to the old file...with new records and dates being captured.
Hi William,
Please see the file attached to see if that's what you had in mind. Note: I've put the two files into separate sheets in the attached file for convenience, but you can have them as separate files.
Mynda
Mynda,
The left outer join seems to do the trick..
Our next step.. we replaced the nulls in the comments section with New Record, which is fine...
but we want to replace the null in the date added field with todays date...havent found syntax for that...
Thank you again!!!!!!!!
Hi William,
You can add a custom column that references the Date column in your data set using this formula (Change [Date] to match the name of your date column):
=if [Date] = null then DateTime.LocalNow() else [Date]
Keep in mind that this is going to update with the current date and time when you refresh Power Query, so you may prefer to actually hard key the date in the formula like this:
=if [Date] = null then #date(2020,2,7) else [Date]
Obviously this will need updating with each query refresh.
Mynda
