What Tool Can I Use To Add/Delete Current Data Vs Previous Data

[kraamerica]

I've tried using Get Transform DATA from File, from Folder, Append/Merge, but I'm either doing something wrong or just don't have the brain power (likely) or expertise as I'm really new to Power Query.

I'm trying to get the days previous data/comments to carry over to the current day data/comments so that each day, the team can work off an updated sheet without having to carry/copy/paste. So for example:

1. I have 2 workbooks with multiple worksheets that have tables and contain the same table format/columns/name
   1. They are mirror to each other, just old vs new data
   2. One workbook is the previous day and the other is the current day data dump
2. I have a row(s) that contain specific unit/reference # (column K or O is the criteria) that is researched with comment and the following toccur:
   1. That row unit/reference # has been cleared through action taken where it will (should) disappear from tomorrows data dump
   2. That row unit/reference # has a comment that further action is needed and the comment column has been updated with that information (and will most likely appear on the next days data dump)

This is the info I need to carry over to the next day (current) example:

1. So if there are 8 rows of data on the previous day, example 4 clear so they should disappear from tomorrow's data, 4 remain with comments
2. Today's data has 10 new rows. 4 of the 10 are from the previous day, 6 are new.
   1. I want the comments to update/carry over/refresy to today's dump IF they are still in today's data dump
   2. The previous day data that does not appear on current day data, will not merge unless it matches to say a cell (or two value) where either a comment was or was not made

The issues:

1. When I've tried the different Get Transform queries, I can get the data to merge, update, remove duplicates, BUT data from yesterday, that cleared still stays on the new data table because of the merge or update
   1. How do I get it to not carry over if it's not on the new day data? (because it cleared overnight)
2. Is there a way to do this not only between the 2 workbooks, but the 6 worksheets (that match in columns) to refresh through a query, then close to a NEW workbook (close and load toa file where I can name. XX.XX_ current_day_data.xlsx)?
3. So in the images, the 1st row from Previous Day is not in the current day data and it shouldn't be, but I don't know how to remove it

I want to Merge data with 2 workbooks (previous & current. to remove any previous data that is no longer in the current data, but also carry over the comments to match any data still in the current data. As well as load it in a new workbook.

Is there a get and transform "conditional" power query route where I can also refresh without going through the editor every time? I have a workbook named "previous day" and "current day", where I would save say 11.3.22 workbook as previous_day_data.xlsx and 11.4.22 that I save as current_day_data.xlsx, then merge, close and load to new workbook with the parameters/conditions I described above?

*I couldn't attach the files, so here is a link to them (please forgive me if against forum rules and I will delete):

Location of the 2 files I'm using 

Thank you in advance!

 Attachments

  

[v-yanjiang-msft]

Hi @kraamerica ,

Please see if this solution can help: Power Query from dynamic URL - Microsoft Community Hub

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kraamerica]

HI and thank you for responding!

Unfortunately no?  The previous date code (>) seems to potentially be right however, this overwrites and does not carry over comments from previous days and wasn't keeping my current data?.  It also sends it to a URL, which I need to export locally.

I'm also assuming the amount of days makes a difference in that I would need to to be < vs >?