
Active Member

October 12, 2019

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.

Active Member

October 12, 2019

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!!!!!!!!


July 16, 2010

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
1 Guest(s)
