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).

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 disappear from tomorrows data dump OR 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 to today's dump IF they are still in today's data dump

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 to)?
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'm just trying to update data with a refresh and I don't know what tool to use. I don't know what to search for anymore on Google or here. I'm just hoping someone could potentially understand what I'm trying to do and what query with what cleanup I should be searching for to learn how to use.

Thank you in advance!

 

[JGordon11]

Here's an example to get you started

let
    tbl1  = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl2  = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    tbl3 = Table.NestedJoin(Table.RemoveColumns(tbl2,{"Comment"}), {"Item"}, tbl1, {"Item"}, "Comment", JoinKind.LeftOuter),
    Result = Table.ExpandTableColumn(tbl3, "Comment", {"Comment"})
in
    Result

Book4
	A	B	C	D	E	F	G	H	I	J
1	Previous Data (Table1)			New Data (Table2)			Keep the comments (PQ output)
2
3	Item	Comment		Item	Comment		Item	Comment
4	1	Comment1		3			3	Comment3
5	2	Comment2		4			4	Comment4
6	3	Comment3		5			5
7	4	Comment4		6			6
8				7			7
9
Sheet2

 

[kraamerica]

Thank you for the quick reply and I hope to be able to work this in later today or tonight.

 

[kraamerica]

Here's an example to get you started

let
    tbl1  = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl2  = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    tbl3 = Table.NestedJoin(Table.RemoveColumns(tbl2,{"Comment"}), {"Item"}, tbl1, {"Item"}, "Comment", JoinKind.LeftOuter),
    Result = Table.ExpandTableColumn(tbl3, "Comment", {"Comment"})
in
    Result

Book4
	A	B	C	D	E	F	G	H	I	J
1	Previous Data (Table1)			New Data (Table2)			Keep the comments (PQ output)
2
3	Item	Comment		Item	Comment		Item	Comment
4	1	Comment1		3			3	Comment3
5	2	Comment2		4			4	Comment4
6	3	Comment3		5			5
7	4	Comment4		6			6
8				7			7
9
Sheet2

Ok, so for me "table 1" is in previous day workbook/worksheet (previous day data) while "table 2" is in another workbook/worksheet (current day data). I can get the comments to "merge", but the data doesn't clean rows that shouldn't be there because they cleared between previous and new data merge.

Also, how do it get Table 3 to load to a new workbookj/worksheet vs within the same sheet?

 

[kraamerica]

Maybe what I'm trying to say is that 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.

Vs code 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?